About SQL join statement

Question

I have three SQL tables: TableA, TableB, and TableC

TableA is a primary key table with foreign keys in TableB and TableC (i.e. TableA is connected to TableB and TableC separately).

Example:

TableA: tableA_id
TableB: tableB_id, tableA_id
TableC: tableC_id, tableA_id

I want to return records from tableA, which matches with the record in tableB (inner join), but returned records are not in tableC. Can this be done with a single SQL statement?

Thanks.

Answer 1

select a.x, b.y
from tablea a
inner join tableb b on a.x = b.x
where not exists (select null 
                  from tablec c 
                  where a.x = c.x)

Answer 2

Think this is one way to do it - everything from a that matches with b, but not in c...

select a.*
  from TableA
  join TableB
    on TableA.tablea_id=TableB.tablea_id
  left outer join TableC
    on TableA.tablea_id=tableC.Tablea_id
 where Tablec.tablea_id is null

Answer 3

You need to add another join for table C, specifically, a left outer join, and then filter for rows where Tablec.tablea_id is null