CODEWITHSUNDEEP
mysqlsql
Mayday
Mayday

Reputation: 5136

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)

---
A|1
A|2
A|3
A|4
B|1
B|3
---

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

NOTE

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

Upvotes: 3

Views: 65

Answers (2)

ignasi
ignasi

Reputation: 443

You could try to play with HAVING and GROUP_CONCAT:

SELECT COL1 
FROM sample
GROUP BY COL1
HAVING GROUP_CONCAT(DISTINCT COL2 ORDER BY COL2 ASC) LIKE '%1,2,3%'

Upvotes: 0

sagi
sagi

Reputation: 40481

You can use the HAVING() clause :

SELECT col1
WHERE Col2 in (1,2,3) 
GROUP BY col1
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.

Upvotes: 5

Related Questions