January 5, 2009 at 12:01 am
hay
There exist 2 tables
table1
A B C
1 554 dd
1 554 fr
1 554 gh
1 453 rf
2 342 rd
3 345 ed
Table2
A D F
1 ed e
1 gd 3
1 er 3
1 de 24
2 et df
3 ed dfd
needed output
A B C D
1 554 dd ed
1 554 fr gd
1 554 gh er
1 453 rf de
2 342 rd et
3 345 ed ed
please suggest as early as possible
January 5, 2009 at 12:31 am
How are the two tables related?
What makes dd relate to ed and fr to gd?
If it's the order of the rows, then what column defines that order?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 5, 2009 at 2:01 am
shiwani2002sg (1/5/2009)
hayThere exist 2 tables
table1
A B C
1 554 dd
1 554 fr
1 554 gh
1 453 rf
2 342 rd
3 345 ed
Table2
A D F
1 ed e
1 gd 3
1 er 3
1 de 24
2 et df
3 ed dfd
needed output
A B C D
1 554 dd ed
1 554 fr gd
1 554 gh er
1 453 rf de
2 342 rd et
3 345 ed ed
please suggest as early as possible
select t1.A,t1.B,t1.C,t2.D
from table1 t1 inner join table2 t2 on (t1.A=t2.A)
January 5, 2009 at 3:40 am
Paresh Prajapati (1/5/2009)select t1.A,t1.B,t1.C,t2.D
from table1 t1 inner join table2 t2 on (t1.A=t2.A)
You may want to test your code, unfortunatelly it doesn't work.
I think it will be better to wait for original poster to answer Gail's questions. 😉
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.January 5, 2009 at 5:02 am
It does not make a great deal of sense, but, for the given data, something like the following should work:
SELECT D1.A, D1.B, D1.C, D2.D
FROM
(
    SELECT ROW_NUMBER() OVER (PARTITION BY A ORDER BY C) AS AID
        ,*
    FROM table1
) D1
    JOIN
    (
        SELECT ROW_NUMBER() OVER (PARTITION BY A ORDER BY E DESC, D DESC) AS AID
            ,*
        FROM table2
    ) D2
    ON D1.A = D2.A
        AND D1.AID = D2.AID
January 5, 2009 at 5:32 am
Works like a charm Ken - at least, it produces the expected output from the given sample - but you need to change SELECT ROW_NUMBER() OVER (PARTITION BY A ORDER BY E DESC) AS AID
to SELECT ROW_NUMBER() OVER (PARTITION BY A ORDER BY D DESC) AS AID
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 5, 2009 at 6:42 am
Hi,
as per you mention for data you want on which column you want to create relationship i think
select t1.A,t1.B,t1.C,t2.D
from table1 t1 inner join table2 t2 on (t1.A=t2.A)
this query will give you output you want but for that you must have same data on both above column
it make's inner join on both table
Raj Acharya
January 5, 2009 at 9:23 pm
hay all
Well i want relation to be generated on the attribute 'A' of both the tables. but in both table A does not include any keys. and its stated that the value of A would be same in both the tables , row-by-row.
I have thought another concept
that i can open 2 cursors , 1 for table 1, and 2 for table2
then create a new table and insert the both table 1 & 2 's values into newly created table,row by row , only then i wud be able to get desired results.
suggestions still required.
January 5, 2009 at 9:25 pm
querry given above for inner join works a bit if i add distinct(t1.A) ..
but not fully
January 5, 2009 at 10:14 pm
PaulB (1/5/2009)
Paresh Prajapati (1/5/2009)select t1.A,t1.B,t1.C,t2.D
from table1 t1 inner join table2 t2 on (t1.A=t2.A)
You may want to test your code, unfortunatelly it doesn't work.
I think it will be better to wait for original poster to answer Gail's questions. 😉
This is the corredct one..
January 6, 2009 at 1:59 am
shiwani2002sg (1/5/2009)
and its stated that the value of A would be same in both the tables , row-by-row.
This looks like homework, but at least you're putting in some thought and effort. Have you tried Ken's solution? It generates your desired output exactly.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 6, 2009 at 7:08 am
shiwani2002sg (1/5/2009)
hay allWell i want relation to be generated on the attribute 'A' of both the tables. but in both table A does not include any keys. and its stated that the value of A would be same in both the tables , row-by-row.
The question is what defines the order. If there's no column that specifies what order you want to control the matches, there's no way to guarantee that order. In SQL order or rows inserted does not determine the order the rows will be returned in, and there's no 'Row number'
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 6, 2009 at 2:25 pm
From the listings given for both tables, I can see no primary key.
So you might first give a serious look at the design of the database.
How do you want to handle the cases where there are duplicate rows in the first table ?
In the second table ? in the first and second table ?
January 6, 2009 at 3:57 pm
I agree... there's nothing in the data presented that identifies which row in the first table goes with which row in the second table other than physical order listed... and we all know that is not maintained in an RDBMS. There must be some other information to do this correctly. All of the solutions rendered so far, are doomed to eventual failure because of the lack of proper join information... unless you don't really care about what the order is.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply