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
 General SQL Server Forums
 New to SQL Server Programming
 Query for 'never in'?

Author  Topic 

Bazinga
Starting Member

19 Posts

Posted - 2013-10-01 : 11:33:24
I would like to do something like this:

SELECT DISTINCT t.ID
FROM table t
WHERE t.code IS NEVER IN 'ABC'

So if the person with ID #000123 has 50 records each with a different code, I only want their ID to appear IF they DONT have a record with code ABC. Not sure how???

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-10-01 : 11:46:04
Try this:

select id
from table
group by id
having sum(case when code='ABC' then 1 else 0 end)=0
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-10-02 : 03:43:54
HAVING clause can be effectively used in above case. For more information refer this http://beyondrelational.com/modules/2/blogs/70/posts/19573/efficient-filtering-with-having-clause.aspx

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -