sql - multiple layers of correlated subqueries
I have table A, B and C
I want to return all entries in table A that do not exist in table B and of that list do not exist in table C.
select * from table_A as a where not exists (select 1 from table_B as b where a.id = b.id)
this gives me the first result of entries in A that are not in B. But now I want only those entries of this result that are also not in C.
I tried flavours of:
select * from table_A as a where not exists (select 1 from table_B as b where a.id = b.id) AND where not exists (select 1 from table_C as c where a.id = c.id)
But that isnt the correct logic. If there is a way to store the results from the first query and then select * from that result that are not existent in table C. But I'm not sure how to do that. I appreciate the help.
You have two WHERE clauses in (the external part of) your second query. That is not valid SQL. If you remove it, it should work as expected:
select * from table_A as a where not exists (select 1 from table_B as b where a.id = b.id) AND not exists (select 1 from table_C as c -- WHERE removed where a.id = c.id) ;
Tested in SQL-Fiddle (thnx @Alexander)
select * from ( select a.*, b.id as b_id, c.id as c_id from table_A as a left outer join table_B as b on a.id = b.id left outer join table_C as c on c.id = a.id ) T where b_id is null and c_id is null
Another implementation is this:
select a1.* from table_A as a1 inner join ( select a.id from table_A except select b.id from table_B except select c.id from table_c ) as a2 on a1.id = a2.id
Note the restrictions on the form of the sub-query as described here. The second implementation, by most succinctly and clearly describing the desired operation to SQL Server, is likely to be the most efficient.
how about using LEFT JOIN
SELECT a.* FROM TableA a LEFT JOIN TableB b ON a.ID = b.ID LEFT JOIN TableC c ON a.ID = c.ID WHERE b.ID IS NULL AND c.ID IS NULL
One more option with NOT EXISTS operator
SELECT * FROM dbo.test71 a WHERE NOT EXISTS( SELECT 1 FROM (SELECT b.ID FROM dbo.test72 b UNION ALL SELECT c.ID FROM dbo.test73 c) x WHERE a.ID = x.ID )
Demo on SQLFiddle
Option from @ypercube.Thank for the present;)
SELECT * FROM dbo.test71 a WHERE NOT EXISTS( SELECT 1 FROM dbo.test72 b WHERE a.ID = b.ID UNION ALL SELECT 1 FROM dbo.test73 c WHERE a.ID = c.ID );
Demo on SQLFiddle