MySQL – How to Select Rows from Table Having More Than Two Specific Values

database-designMySQLmysql-workbenchquerysubquery

I have a table having details such that

Userpin  product       Time1      contains
4556     23            7:30         A
4557     23            7:30         B
4558     23            7:30         C
4559     23            7:30         D
4560     23            7:30         E
4561     24            7:30         A
4562     24            7:30         B
4563     24            7:30         C
4564     24            7:30         D
4565     24            7:30         E
4566     25            7:30         A
4567     25            7:30         A
4568     23            7:30         F
4568     23            7:30         G

So with the above table, I need all products that contains (A,B,C,D,E,F) all of them.

With the given table, I am expecting 23 as the result (Although product 23 contains G as well, but that does not bother me.).

Best Answer

I have recreated your table in tempdb using the Adventure Works Database. This code basically selects all the records that have column contains ('A', 'B', 'C', 'D', 'E', 'F') and then does a distinct count on the [contains] column. When the result is six the record must have a row of all of ('A', 'B', 'C', 'D', 'E', 'F').


Create Table #Table (Userpin int, product int, [time] time, [contains] varchar(1))
Insert into #Table (Userpin, product, [time], [contains])
Select 4556,     23,            '7:30',         'A' UNION ALL
Select 4557,     23,            '7:30',         'B' UNION ALL
Select 4558,     23,            '7:30',         'C' UNION ALL
Select 4559,     23,            '7:30',         'D' UNION ALL
Select 4560,     23,            '7:30',         'E' UNION ALL
Select 4561,     24,            '7:30',         'A' UNION ALL
Select 4562,     24,            '7:30',         'B' UNION ALL
Select 4563,     24,            '7:30',         'C' UNION ALL
Select 4564,     24,            '7:30',         'D' UNION ALL
Select 4565,     24,            '7:30',         'E' UNION ALL
Select 4566,     25,            '7:30',         'A' UNION ALL
Select 4567,     25,            '7:30',         'A' UNION ALL
Select 4568,     23,            '7:30',         'F' UNION ALL
Select 4568,     23,            '7:30',         'G' 

Select product, count(distinct [contains]) as count_distinct_contains
from #Table
where [contains] In ('A', 'B', 'C', 'D', 'E', 'F')
GROUP BY product 
HAVING count(distinct [contains]) = 6

This is the Microsoft SQL Server syntax.

Related Question