CODEWITHSUNDEEP
sqlsubquery
codingknob
codingknob

Reputation: 11660

sql - confusion with correlated subqueries

I have been using suggestions given to me in an earlier question regarding subqueries. See here:

sql - multiple layers of correlated subqueries

SQL Server : left join results in fewer rows than in left table

I am using ms sql server (I believe it is 2005).

What I am trying to do now is the following:

I have a result of rows (call it result A), that is obtained from doing the following:

select * from TableA as a
join (select * from TableB where RealDate = '4/20/2013 12:00:00 AM') as b
on a.id = b.id

Of this result I want to find all rows that are NOT in the result of rows returned by this query:

select * from TableA as a
join TableC as c
on c.id = a.id

Essentially I have a situation where the first query results in 246 records, while the second query results in 247 records. I was expecting the first result to return 247 records (all of which should be in the list of records returned by the second query). So now I need to investigate which record is missing so I can take proper action.

I tried to do something like the following but received various errors:

select * from (select * from TableA as a
join (select * from TableB where RealDate = '4/20/2013 12:00:00 AM') as b
on a.ul_id = b.id))
as result_A
where not exists (select 1 from (select * from TableA as a
                  join TableC as c
                  on c.id = a.ul_id) as result_B
                  where result_A.ul_id = result_B.id);

Upvotes: 0

Views: 148

Answers (2)

Dan Bracuk
Dan Bracuk

Reputation: 20804

Your narrative says you want to exclude results from:

select * from TableA as a
join TableC as c
on c.id = a.id

but your not exists has a different subquery. Also, some of your errors may have been caused by using the same alias more than once.

Finally, Pieter's approach should work. I didn't check the details.

Upvotes: 1

Pieter Geerkens
Pieter Geerkens

Reputation: 11883

Do this set difference:

select a.* from TableA as a
join TableC as c
on c.id = a.id

except

select a.* from TableA as a
join (select * from TableB where RealDate = '4/20/2013 12:00:00 AM') as b
on a.id = b.id

If for some reason this does not return a row, then the first query has a duplicate.

Upvotes: 2

Related Questions