CODEWITHSUNDEEP
sql-server
skyline01
skyline01

Reputation: 2101

Filtering with multiple joins

I am working in SQL Server 2008. I know that, when joining 2 tables (call them A and B), if I want to return all records that are in A but are not in B, I use:

SELECT
columns
FROM A
LEFT JOIN B
ON A.a_key = B.b_key
WHERE B.b_key IS NULL

In my particular situation, I am trying to join 3 tables, where one of them is an intermediate table. Let's call them A, B, and C. (B is the intermediate table.) Here are the important columns:

A: colA1, colA2

B: colB1, colB2

C: colC1, colC2

B.ColB2 matches A.ColA1, and C.colC1 matches B.colB1. My goal is to return all records in A that are not in C. So, my best guess right now is:

SELECT
columns
FROM A
LEFT JOIN B
ON A.colA1 = B.colB2
LEFT JOIN C
ON B.ColB1 = C.colC1
WHERE C.colC1 IS NULL

I know that there are some records in A that are not in C. However, my query doesn't return those records. What am I doing wrong? My best guess at the moment is that my joins are wrong, since B is an intermediate table.

Upvotes: 0

Views: 38

Answers (2)

Pரதீப்
Pரதீப்

Reputation: 93704

First INNER JOIN the Table B and Table C. Then do LEFT JOIN the result with Table A you will get the records. Try this.

SELECT * 
FROM   a 
       LEFT JOIN (SELECT * 
                  FROM   b 
                         JOIN c 
                           ON b.colb1 = c.colc1) Scd 
              ON a.cola1 = scd.colb2 
WHERE  scd.colb2 IS NULL

Upvotes: 1

benji
benji

Reputation: 606

Actually I have never seen this kind of syntax where you compare the keys and add the condition forcing the key you're joining with to be null. Also - left join doesn't only return the records that are not in the other table you're joining with, it just doesn't drop those that have no match, and instead fills the rest of the columns with null values. But it also keeps records that matched in both tables. You can go here for a clear example.

It doesn't sound like left join is the way to go. Why not do regular join and use not it like this:

select columns
from A, B
where A.colA1 = B.colB2
and B.colB1 not in (select C.colC1 from C)

Upvotes: 0

Related Questions