CODEWITHSUNDEEP
mysqlsqlmysql-workbench
sunnygud
sunnygud

Reputation: 3

list of those Customer IDs from Table X who have bought all of the products from Table Y?

I have 2 Tables X & Y,

Table X(has 5 records)

Cust_Id  Prod_Key 
 1        'A'
 1        'D'
 2        'C'
 3        'D'
 2        'B'

Table Y (has 2 records)

Prod_Key 
  'A'
  'D'

Need SQL to list the Cust_ID which has all the products from Table Y. ~ Cannot hard code as the table keeps updated

Upvotes: 0

Views: 1153

Answers (5)

Shushil Bohara
Shushil Bohara

Reputation: 5656

You can do the same thing using INNER JOIN with GROUP BY and COUNT as below 

SELECT x.Cust_Id
FROM X x
INNER JOIN Y y ON y.Prod_Key = x.Prod_Key
GROUP BY x.Cust_Id
HAVING COUNT(y.Prod_Key) = (SELECT COUNT(*) FROM Y)

Upvotes: 1

honey
honey

Reputation: 53

try this :

select * from x where prod_key in (select prod_key from Y)

Upvotes: 0

Manoj De Mel
Manoj De Mel

Reputation: 995

You can try the following code to get the unique list of Cust_IDs

select DISTINCT Cust_Id from X where x.Prod_key in (select y.Prod_key from Y)

Upvotes: 0

Rajat Mishra
Rajat Mishra

Reputation: 3770

You can also try inner join as you need only the customerid whose productkey is present in table y

SELECT x.Cust_Id 
FROM x 
INNER JOIN y
ON x.Prod_Key = y.Prod_key

Upvotes: 0

Ankur Patel
Ankur Patel

Reputation: 1423

You can try the following:

select 
  cust_id 
from 
  Y
LEFT OUTER JOIN 
  X 
  on Y.Prod_key = X.Prod_key
Group by 
  Cust_id
having 
 count(DISTINCT X.Prod_key) = (select count(*) from Y);

SQL FIDDLE DEMO

Upvotes: 3

Related Questions