sql - confusion with correlated subqueries

Question

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);

Pieter Geerkens · Accepted Answer · 2013-04-20 20:56:59Z

2

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.

answered Apr 20, 2013 at 20:56

Pieter Geerkens

11.9k2 gold badges35 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dan Bracuk · Accepted Answer · 2013-04-20 21:00:06Z

1

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.

answered Apr 20, 2013 at 21:00

Dan Bracuk

20.8k5 gold badges30 silver badges44 bronze badges

Collectives™ on Stack Overflow

sql - confusion with correlated subqueries

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related