JOIN - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

JOIN

Author

Topic

daidaluus
Yak Posting Veteran

73 Posts

Posted - 2012-05-11 : 11:03:25

i have 2 tables with the following data:

id1 value1
--- ------
1 1000
2 1000
3 1000
4 2000
5 2000

and

id2 value2
--- -----
101 1000
102 1000
103 2000
104 2000
105 2000

now i need a query to return this result set:

id1 value1 id2 value2
--- ----- --- ------
1 1000 101 1000
2 1000 102 1000
3 1000 NULL NULL
4 2000 103 2000
5 2000 104 2000
NULL NULL 105 2000

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2012-05-11 : 11:05:48

Are you really using SQL Server 2000? The answer is much simpler if you are on 2005 or newer.

- Jeff
http://weblogs.sqlteam.com/JeffS

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-11 : 15:55:10

[code]
SELECT id1, value1, id2, value2
FROM
(
SELECT *,COALESCE((SELECT COUNT(*) FROM Table1 WHERE value1= t.value1 AND id1<t.id1),0) +1 AS Rn
FROM table1 t
)t1
FULL OUTER JOIN
(
SELECT *,COALESCE((SELECT COUNT(*) FROM Table2 WHERE value2= t.value2 AND id2<t.id2),0) +1 AS Rn
FROM table2 t
)t2
ON t2.Rn = t2.Rn
AND t2.value2 = t1.value1

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources