Avatar of milani_lucie
milani_lucie
Flag for United States of America 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
Microsoft SQL ServerMicrosoft SQL Server 2005Microsoft SQL Server 2008

Avatar of undefined
Last Comment
Theo Kouwenhoven

8/22/2022 - Mon
SOLUTION
sachinpatil10d

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
jogos

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes