Customers who purchased products (1 or 2 or 3) but not (4 or 5) - SQL Server

milani_lucie
milani_lucie used Ask the Experts™
on
Hi,

I have CustProd table having Customers with Products information. Can you please provide me the SQL query for:

Customers who purchased products (1 or 2 or 3) but not (4 or 5)

Please note that customers may purchase Product "1" and Product "4" or Product "5". We want customers who purchased ONLY 1 or 2 or 3 but not "4" or "5".

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Try this

select * from Customer where CustId in (
select CustID from CustProd where CustID not in (
select CustId from CustProd where  ProdId not in (1,2,3)))

Open in new window

Commented:
select c.* 
from dbo.Customer c 
where exists 
 ( select cp1.CustID from dbo.CustProd as cp1  
  where cp1.CustID = c.CustId  and cp1.ProdId in (1,2,3))
and not exists 
 ( select cp2.CustID from dbo.CustProd as cp2  
   where cp2.CustID = c.CustId  and cp2.ProdId in (4,5))
 

Open in new window

Theo KouwenhovenApplication Consultant

Commented:
If you need only the CustProd then

select * 
  from CustProd 
 where ProdId in (1,2,3) 
   and ProdId not in (4,5)

Open in new window


If CustProd is a part of the selection on a Customer table

Select * 
  from Customer c
 Where 
     (select count(*) 
        from CustProd p 
       where p.ProdId in (1,2,3) 
         and p.ProdId not in (4,5)
         and c.CustId = p.CustId) > 0

Open in new window

Commented:
@murphey
When first line is true, second always is true, they must come from differet records
where ProdId in (1,2,3) 
   and ProdId not in (4,5)

Open in new window

Application Consultant
Commented:
jogos give you the right answer, but most of the time  a count(*) is faster then an Exist(), don't know why :-)

Select * 
  from Customer c
 Where 
     (select count(*) 
        from CustProd p 
       where p.ProdId in (1,2,3) 
         and c.CustId = p.CustId) > 0
   and 
     (select count(*) 
        from CustProd p 
       where p.ProdId in (4,5)
         and c.CustId = p.CustId) = 0

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial