Mayday Mayday - 1 year ago 62
MySQL Question

How to select values grouped by column if another column has multiple values

I am trying to get from a table those values in column1, that have some values in column2.

For example, I want to get A because it has "1,2,3". (Expected Output of select: A)


This is what I've tried

SELECT * FROM sample_table WHERE Col2 in (1,2,3) GROUP BY col1

I understand perfectly it won't work, since I am just putting the condition that column 2 must have one of those values, and then I group them by Col1, so It will take (A,1),(A,2),(A,3),(B,1),(B,2) -> group them by Col1 and give me (A) and (B)

I really have no clue how I could force it to have ALL the 3 values

Here is the fiddle to try it


Please feel free to edit the post in order to make it more understandable, I have tried my best, but I know its not very well explained, specially the title

Thank you very much

Answer Source

You can use the HAVING() clause :

WHERE Col2 in (1,2,3) 
HAVING COUNT(col2) = 3

This will show only results that have 3 values , and since you filtered only for 1,2,3 , if there are 3, it those 3.

Note : If the values on col2 are not distinct , E.G. it's possible for this data to appear:

A | 1
A | 1
A | 2

Then add DISTINCT inside the parentheses of the count.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download