select with having to include and exclude

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

select with having to include and exclude

Author

Topic

workindan
Starting Member

21 Posts

Posted - 2012-02-22 : 13:53:54

Looking to use Having() at the end of a select query to include a set of values and exclude a set of values...right now I have:

(Sybase)

************

SELECT p.ID
FROM people as p inner join items i on p.item_id = i.item_id
WHERE p.order_date between '2012-02-20' and '2012-02-22'
GROUP BY p.ID
having count(case when i.item_id in ( set1 of item id's ) then 1 else 0 end) > 0
and count(case when i.item_id in ( set2 of item id's ) then 1 else 0 end) > 0
and count(case when i.item_id in ( set3 of item id's ) then 1 else 0 end) = 0

************

So basically I'm looking for people who ordered items with something from set 1 and something from set 2 but did NOT order anything from set 3.

When I run this, I get zero results. If I run only the inclusion sets (1 and 2) I get results. If I change set3 count to > 0 to include it, I get results that include set 3.

Why can't I use a count of zero to exclude people who ordered something from set 3?

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-22 : 14:25:11

What does set1,2,3 actually mean?

SELECT * FROM Table
WHERE Item_ID IN (SELECT Id From Set1)
AND Item_ID IN (SELECT Id From Set2)
AND Item_ID NOT IN (SELECT Id From Set3)

????

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

workindan
Starting Member

21 Posts

Posted - 2012-02-22 : 14:31:02

Sorry, it's just a set of ID #'s

SELECT p.ID
FROM people as p inner join items i on p.item_id = i.item_id
WHERE p.order_date between '2012-02-20' and '2012-02-22'
GROUP BY p.ID
having count(case when i.item_id in ('20034','20035','20036',... ) then 1 else 0 end) > 0
and count(case when i.item_id in ( '20042','20043','20044',.... ) then 1 else 0 end) > 0
and count(case when i.item_id in ( '67889','67890',67891',... ) then 1 else 0 end) = 0

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-22 : 15:00:18

>>> something from set 1 and something from set 2 but did NOT order anything from set 3.

SELECT * FROM Table
WHERE Item_ID IN ('20034','20035','20036',... )
AND Item_ID IN ('20042','20043','20044',.... )
AND Item_ID NOT IN ('67889','67890',67891',... )

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources