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
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
JOIN
Posted - 05/11/2012 : 11:03:25
