Never Back Down Never Back Down - 2 months ago 6
MySQL Question

Mysql - How to retrieve all items that doesn't have a specific attribute?

I have a table as below



id key value section
-----------------------------------
CT1 | A | 25 | M-2
CT1 | B | 35 | M-1
CT2 | A | 70 | M-1
CT2 | C | 30 | M-2
CT2 | D | 20 | M-3
CT3 | B | 40 | M-2
CT3 | A | 15 | M-1
CT4 | B | 25 | M-1
CT4 | D | 25 | M-2





I need a way to return all ids which doesn't have certain key. For Example:

Ids that dont have 'D':
CT1, CT3,

Is it possible to fetch this result from a MySQL query? I have searched for earlier questions as well. Couldn't find anything close.

Answer

You can use grouping with a HAVING clause:

SELECT id
FROM mytable
GROUP BY id      
HAVING COUNT(CASE WHEN key = 'D' THEN 1 END) = 0      
Comments