Mysql Question posted in
The official MySQL documentation can be found here.

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')
Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    here a other query for you:

    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'),
(101, 'Day 1', 'Event Name'),
(101, '2', 'Event Number'),
(102, 'Day 2', 'Event Name'),
(102, '1', 'Event Number'),
(103, 'Day 1', 'Event Name'),
(103, '1', 'Event Number');


SELECT  t.id FROM    TABLE_STRINGS t
WHERE   (t.keyValue = 'Event Name' AND t.value = 'Day 1') 
AND EXISTS (
      SELECT 1 FROM TABLE_STRINGS t2
      WHERE t.id = t2.id
      AND (t2.keyValue = 'Event Number' AND t2.value = '1') 
);

    sample DBFiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search