Re: SUB-SELECT Nightmares!
From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 01/05/05
- Next message: scott: "What is N?"
- Previous message: *** mus: "newbie, @@error does not seem to work"
- In reply to: Carl Howarth: "SUB-SELECT Nightmares!"
- Next in thread: Carl Howarth: "Re: SUB-SELECT Nightmares!"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 05 Jan 2005 21:26:30 +0100
On Wed, 5 Jan 2005 17:08:25 -0000, Carl Howarth wrote:
> (SELECT COUNT(A.*) FROM Table1 A WHERE A.Test NOT IN
>(SELECT Test FROM Testing)) AS ResultsB
Hi Carl,
As Joe Celko already indicates, this subquery is totally unrelated to the
campaigns, so this will produce a grand total. If this displays 17 on the
report for CampaignID 1, it will also be 17 for each other campaign - and
it will be 17 on a report over all compaigns. That might explain the
mismatch you found on your totals.
Another thing is that NOT IN queries are dangerous if you don't fully
understand how NULLS behave. I'll spare you the long explanation; the
bottom line is that no row will ever match a NOT IN subquery if at least
one of the values in the subquery is NULL. So unless Testing.Test has a
NOT NULL constraint, I'd suggest you rewrite using NOT EXISTS or using an
outer join.
Best, Hugo
-- (Remove _NO_ and _SPAM_ to get my e-mail address)
- Next message: scott: "What is N?"
- Previous message: *** mus: "newbie, @@error does not seem to work"
- In reply to: Carl Howarth: "SUB-SELECT Nightmares!"
- Next in thread: Carl Howarth: "Re: SUB-SELECT Nightmares!"
- Messages sorted by: [ date ] [ thread ]
