MySQL - Joining a table based on 2 keys from a Key/Value table

Question

I have a table for settings, which can be shown as: object_id (int) key (varchar) value (text)

I am trying to grab the object_id that has key's equal to 2 items.

SELECT `object_id` FROM `settings` WHERE `key` = 'A' AND `key` = 'B'

I know that won't work, the only way I can think of doing this is joining it on itself:

SELECT a.`object_id` FROM `settings` AS a LEFT JOIN `settings` AS b ON ( a.`object_id` = b.`object_id` ) WHERE a.`key` = 'A' and b.`key` = 'B'

While I haven't tested the last statement, I'm sure something like this could work. This query will be performed on potentially hundreds of thousands of records every hour, so I want to keep it optimized -- is there a better way to do this?

Answer 1

Oh, you want:

  SELECT `object_id` 
    FROM `settings`  
   WHERE (`key` = 'A' AND `value`='foo') 
      OR (`key`= 'B' AND `value`='bar')
GROUP BY `object_id`
  HAVING COUNT(*) = 2

Use:

  SELECT `object_id` 
    FROM `settings`  
   WHERE `key` IN ('A', 'B')
GROUP BY `object_id`
  HAVING COUNT(DISTINCT `key`) = 2

Or:

SELECT x.`object_id` 
  FROM `settings` AS x 
  JOIN `settings` AS y ON y.`object_id` = x.`object_id`  
 WHERE x.`key` = 'A' 
   AND y.`key` = 'B'