Easy SQL Left Join Problem! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Easy SQL Left Join Problem!

Author

Topic

NewCents
Starting Member

19 Posts

Posted - 2006-01-15 : 12:49:33

I've got a cases table:
cases_ID, notes
1, 'hello'
2, 'testing'
3, 'help'
4, 'more help'

and a casedetail table:
casedetail_ID, cases_ID
1, 3
2, 3
3, 1

I'm looking for a statement to produce only the cases which do not have a casedetail_ID associated with them.
So the results of the query would be:
2, 'testing'
4, 'more help'

This statement:
SELECT cases.cases_ID, count(casedetail_ID) as casedetail_ID
FROM cases
left join casedetail on cases.cases_ID=casedetail.cases_ID
group by cases.cases_ID
having sum(casedetail_ID)>0

produces 4,000+ rows.

And even though there are cases in the system with no casedetails trying this:
SELECT cases.cases_ID, count(casedetail_ID) as casedetail_ID
FROM cases
left join casedetail on cases.cases_ID=casedetail.cases_ID
group by cases.cases_ID
having sum(casedetail_ID)=0

produces nothing?

I simply want to return cases that have no casedetail items.
Any help appreciated!

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2006-01-15 : 14:11:04

That won't work because the SUM of >= 1 NULLs is NULL, not 0.
However, since you know that there will only be one row in the output for cases that don't have details, and that casedetails.cases_id will be NULL on these rows, you don't really need to GROUP anything at all.


SELECT cases.cases_ID, 0 AS casedetail_ID
FROM cases 
LEFT JOIN casedetail ON cases.cases_ID = casedetail.cases_ID
WHERE casedetail.cases_ID IS NULL

Though I don't really see the point of the second column in the output, since it (a) can't be anything but zero (b) is consequently misnamed.

NewCents
Starting Member

19 Posts

Posted - 2006-01-15 : 14:33:03

Arnold,

Thank you, this is exactly what I was looking for!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources