CODEWITHSUNDEEP
mysql
Shades88
Shades88

Reputation: 8360

Mysql self join help needed

I have following table structure:

uid    | product_code
---------------------
00001  | 'tb'
00001  | 'im' 
00001  | 'ip'
00002  | 'tb'
00002  | 'im'

I want a selfjoin query that will show me those uids which have all three product codes 'mg','ip','tb' with them. i.e only 00001 will be displayed. How can it be done?

Upvotes: 1

Views: 78

Answers (3)

Dojo
Dojo

Reputation: 5704

select uid from mytable 
where product_code in ('mg','ip','tb') 
group by uid having count(distinct(product_code))=3;

Upvotes: 1

Argeman
Argeman

Reputation: 1353

SELECT sj1.uid FROM tbl sj1 
    JOIN tbl sj2 ON sj1.uid = sj2.uid 
    JOIN tbl sj3 ON sj1.uid = sj3.uid
WHERE sj1.product_code = 'tb'
    AND sj2.product_code = 'im'
    AND sj3.product_code = 'mg'

should work also if you have more product codes.

Upvotes: 2

Shehzad Bilal
Shehzad Bilal

Reputation: 2523

If you have only three product code Select * from table group by uid having count(uid) = 3

Upvotes: 0

Related Questions