December 16, 2006 at 4:46 pm
I am really drawing a blank on this one, and I was hoping that someone could help. I have three tables InspectionsComplete, ClerkCitations, and OutletCitations. The scenario is that when an inspection is complete and there is a citation issued, either clerk or outlet, the necessary information is stored in the corresponding ClerkCitations or OutletCitations tables that are all related to the InspectionsComplete table through the InspectionID. What I need is all of the inspections that did not have a citation issued.
Any ideas...
December 16, 2006 at 6:27 pm
There's many ways to structure it...
In the WHERE clause, you can do:
where not InspectionID in (Select InspectionID from ClerkCitations) and not InspectionID in (Select InspectionID from OutletCitations)
or you could left join on both tables and in the WHERE clause do:
where ClerkCitations.CitationID is null and OutletCitations.CitationID is null
or you could put a subquery in the SELECT clause returning the Citation count
The best bet for performance is to put a flag or a count in a field in the InspectionsComplete table at the time you insert the data and query against that.
Because these inspections are a slice of history I don't think you have to worry about normalization issues (but that's just me.)
December 18, 2006 at 10:03 am
Thanks much. I ended up using the top solution. Thanks again...
December 18, 2006 at 10:57 am
Try using (not) exists instead of (not) in. The exists halts when it finds one record, in requires finding them all before comparision.
December 18, 2006 at 11:44 am
True. If you're not going to use my final recommendation, NOT EXISTS is probably the way to go.
I wouldn't be surprised if SQL Server optimized all three (NOT IN, NOT EXISTS, LEFT JOIN... WHERE NOT... IS NULL) to the same plan, though.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply