Leon Weemen Leon Weemen - 13 days ago 5
MySQL Question

MySQL: exclude if some column contains value for some_id X

Heey all, I'm struggling with something very simple but I cant find the correct query.

I have this table

id | col_a | col_b
-----------------
1 | 1 | value_a
2 | 1 | value_b
3 | 1 | value_c
4 | 2 | value_a
5 | 2 | value_b
6 | 3 | value_a
7 | 3 | value_b


What I want is all distinct values from col_a where none of the records where col_a is some_id has no "value_c" in col_b

so in the case I want to get following resultset

col_a
-----
2
3


because col_b has a "value_c" where col_a = 1 (record id:3)

so I basicly I'm not interested in the id of col_a if any record with that id contains "value_c" in col_b

I hope's clear

already thanks in advance!!

Answer

The "textbook solution" would probably be to use a not exists operator:

SELECT DISTINCT col_a
FROM   mytable a
WHERE  NOT EXISTS (SELECT *
                   FROM   mytable b
                   WHERE  a.col_a = b.col_a AND col_b = 'value_c')

But I think that grouping and counting the value_c instances would be a tad more elegant:

SELECT   col_a
FROM     mytbale
GROUP BY col_a
HAVING   COUNT(CASE col_b WHEN 'value_c' THEN 1 END) = 0