Query to get a list of parent records that have no child records
-
Hi from a newbie,
I hope somebody out there can help with a problem that has had my head spinning for days.
I have a SQL 2000 database with the tables CASE and ACTION.
CASE has the following structure:
- ID int
- CREATEDATE datetime
- CASETITLE varchar (40)
ACTION has the following structure
- ID int
- CASEID int
- ACTIONTEXT varchar (40)
All rows in the child ACTION table have their CASEID column set to the ID of their parent record in the CASE table, i.e. the link between the tables is ACTION.CASEID = CASE.ID.
Normally there are a number of rows in the ACTION table for each record in the CASE table. However, I need a SQL query to list the anomalies where a parent CASE record has NO child ACTION records. The added complication is that I need to be able to specify the date range I am interested in based on the CREATEDATE column in the CASE table.
I am probably being really stupid in not being able to figure this out but I really hope someone can help.
Cheers
Inj
-
SELECT Id, CreateDate, CaseTitle
FROM [Case] c
WHERE NOT EXISTS
(SELECT *
FROM [Action]
WHERE Id = c.Id)
AND CreateDate >= '20030101' AND CreateDate < '20040101'
--Jonathan -
If you use the join method the query will execute much faster.
-
If the relationship of Case to Action is "One to Many" (and I would assume it is) then using "Where Not Exists" with a "Top 1" may be more efficient then a left join:
SELECT Id, CreateDate, CaseTitle
FROM [Case] c
WHERE NOT EXISTS
(SELECT top 1 1 FROM [Action] a
WHERE a.Id = c.Id)
AND c.CreateDate >= '20030101' AND c.CreateDate < '20040101'
The only way to tell is to test it.
Signature is NULL
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply