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.

Answers


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)


Try this:

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


Need Your Help

Implementation of Union find Algorithm

c++

I have a series of Key value pairs. Each Key has 2 values. Values of Keys may coincide.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.