Search code examples
mysqlkeykey-value

MySQL Query with 2 Qualified Key Value Pairs

I want to return the id 100 when ONLY BOTH of the pairs exist. This does not return a value but only using one of the pairs does return a result. How to make both pairs qualify for returning id of 100?

CREATE TABLE TABLE_STRINGS
(`id` int, `value` Varchar(7), `keyValue` varchar(20))
;

INSERT INTO TABLE_STRINGS
(`id`, `value`, `keyValue`)
VALUES
(100, 'Day 1', 'Event Name'),
(100, '1', 'Event Number');


SELECT  id
FROM    TABLE_STRINGS 
WHERE   (keyValue = 'Event Name'
    AND 
    value = 'Day 1') 
    AND 
    (keyValue = 'Event Number'
    AND 
    value = '1')
Solution

  • One canonical way to do this uses aggregation:

    SELECT id
FROM TABLE_STRINGS
GROUP BY id
HAVING
    SUM(keyValue = 'Event Name' AND value = 'Day 1') > 0 AND
    SUM(keyValue = 'Event Number' AND value = '1') > 0;

    Each sum assertion in the HAVING clause only returns true if a given key/value pair appears for that id.