homeryansta

Give me some ideas IT guys. I'm stuck trying to figure this out on a Sunday!!!

Given set A and set B where (A != B)

Set A is given in comma delimited form:
ID,Value
---------
1,1
1,2
2,2
3,1

Set B is given in comma delimited form:
ID,Value2
----------
1,1
1,3
1,4
2,2
3,1
3,2

I want the result set of A and B merged to look like this

ID,Value,Value2
------------------
1,1,1
1,2,3
1,null,4
2,2,2
3,1,1
3,null,2

Notice that I want to join the ID in the order they are displayed.
If I do a "full outer join" on the ID, I get 6 results for ID 1 which is not what I want.
If I do a union on both sets with dummy columns to match them up, I will still get 5 results for ID 1.

Any ideas? I can barely think anymore.



The IDs have to match, but doesn't cross match like in a traditional join.

Example:
if A has 3 rows with ID 1
and B has 1 row with ID 1

match the first row in A to the only row in B
and then just display the rest of A.

In a traditional join, all 3 rows in A would be join to the single row in B.

__________________