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
|