Set Theory + IT question

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

homeryansta

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!

__________________