CODEWITHSUNDEEP
sql-server
skyline01
skyline01

Reputation: 2101

SQL join multiple tables - result set not expected

I am working in SQL Server 2008. I have 4 tables I want join. Let us call them tables A, B, C, and D. B, C, and D are all subsets of table A. There could be some records that are common amongst B, C, and D. My goal is to select all records in A that are not in B, C, or D. So, I think the correct query to run is:

SELECT
    A.x
FROM A
LEFT JOIN B
ON A.x = B.y
LEFT JOIN C
ON A.x = C.z
LEFT JOIN D
ON A.x = D.i
WHERE 
(
(B.y IS NULL)
AND
(C.z IS NULL)
AND
(D.i IS NULL)
)

The problem I am having is that I know that there are some records in table B that are returning in this result set which should not be. (The same could hold for tables C and D as well.) So, something must be wrong with my query. My best guess is that the joins are vague. The first one should give me all records in A that are not in B. Similarly, the second one should give me all records in A that are not in C. Because I have used AND in the WHERE clause, then I should essentially be returning only the records that are common to each of the joins. But, something is going wrong. How do I correct this?

Upvotes: 0

Views: 55

Answers (1)

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

Try this:

SELECT x FROM A
EXCEPT
SELECT x FROM
(
   SELECT y FROM B UNION
   SELECT z FROM C UNION
   SELECT i FROM D
) T(x)

Upvotes: 1

Related Questions