CODEWITHSUNDEEP
sql-servert-sql
Talay
Talay

Reputation: 381

TSQL - Find Customers who purchased two sets of Products

I have a table with values like this:

Customer    Product#
   A           1
   A           3
   A           5
   A           7
   A           11
   A           13
   B           5
   B           11
   B           13
   C           4
   C           5
   C           6
   C           11
   C           14
   C           42
   C           60

I would like a query that will give me all customers who have:

a. Either Product# 1 OR 5
AND
b. BOTH Product# 11 AND 13.

I would appreciate any help or suggestions.

Thanks!

Upvotes: 2

Views: 415

Answers (2)

Gurwinder Singh
Gurwinder Singh

Reputation: 39457

Try this:

select customer
from your_table
group by customer
having count(distinct case 
            when product# in (11, 13)
                then product#
            end) = 2
    and count(case 
            when product# in (1, 5)
                then 1
            end) > 0

grouping by customer to find if both 11 and 13 are present, use conditional aggregation and similarly, check if there is atleast one row with 1 or 5 for the customer.

Upvotes: 4

Franz Stoneking
Franz Stoneking

Reputation: 35

'HAVING' works but you could also do this with a couple of subselects- longer and less efficient but easier to document and/or use with more complex outputs.

SELECT DISTINCT
     a.customer
FROM 
     your_table AS a
WHERE 
     a.customer IN ( SELECT customer FROM your_table WHERE product# IN (1,5) )
     AND a.customer IN ( SELECT customer FROM your_table WHERE product# = 11)
     AND a.customer IN ( SELECT customer FROM your_table WHERE product# = 11)

Upvotes: 0

Related Questions