asked on

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

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

SOLUTION

sachinpatil10d

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

jogos

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Theo Kouwenhoven

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

jogos

@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

ASKER CERTIFIED SOLUTION

Theo Kouwenhoven

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial