Quote:
Originally Posted by Little Blue
This is with MS Access SQL, but SQL Server should be very similar. I struggled with this a lot the first time I needed to do it years ago. The trick is to build a set of all master combinations with a UNION, then use that as the base for the Left Joins.
SELECT x.Id, a.Value, b.Value
FROM ((
SELECT Id, Value
FROM A
UNION
SELECT Id, Value
FROM B
) as x
left join a on x.id = a.id and x.value = a.value)
left join b on x.id = b.id and x.value = b.value
Id a.Value b.Value
1 1 1
1 2
1 3
1 4
2 2 2
3 1 1
3 2
|
That won't work. The reason is because the solution I want is a 1-1 relationship between a m-m set.
Here is how I solved it.
I partitioned the IDs and assigned unique keys(SubID) to them..
That is:
ID,Value2,SubID
----------
1,1,1
1,3,2
1,4,3
2,2,1
3,1,1
3,2,2
I do the same thing to the other set.
The resulting set after I do a full outer join on ID and SubID is exactly what I wanted.
Thanks for the help folks!