Evangelito Evangelito - 5 months ago 7
MySQL Question

SQL select the only rows with only a certain values in them

i have the same problem here :SQL select rows with only a certain value in them

but the solution that i have found work only for one value given, i mean that i want it to work for a number of values {2 or +}, for example :

Col 1 Col 2 Col 3
1 A 1
2 A 2
3 B 1
4 C 1
5 C 2
6 D 1
7 C 3


I want to get all rows that just contain cols3 = 1 and cols3 =2 ONLY , (just values 1 and 2) and the output will be : "A" for the table given, i dont want to have C because it has another values of cols 3 != of 1 or 2,
The solution in the link allows doing this for just one value , but i want to extend it to 2 value or plus , can someone help me please :D

Answer

You can use conditional aggregation for this:

select col2
from yourtable
group by col2 
having sum(col3=1) > 0 
  and sum(col3=2) > 0
  and sum(col3 not in (1,2)) = 0 
Comments