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

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?

From stackoverflow Kerry

• 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'
  

  Kerry : Thanks -- exactly what I was looking for -- would it be possible to specify values to match those keys? if `key` = 'a' AND `value` = 'b'?

  OMG Ponies : @Kerry: Sorry? Those are distinct columns & values, no need for a JOIN or GROUP BY/HAVING

  Kerry : Yeah -- my mind is a bit muddled right now, drawing a few blanks, thanks -- your solutions are perfect (didn't know the first was possible)

  Wrikken : @Kerry: yes, just add as seperate clauses: ` WHERE (\`key\` = 'A' AND \`value\`='foo') OR (\`key\`= 'B' AND \`value\`='bar')`

  Kerry : @ Wrikken -- its needs both keys to exist and have designated value -- not one or the other. I worded the question poorly but @OMG Ponies answered the question as I asked it which gave me some good data to work with.

  OMG Ponies : @Kerry: So you mean: `WHERE `key` IN ('A', 'B') AND `value` = 'foo'`? That's fine as is

  Kerry : `key = 'a' AND value = 'foo' AND key = 'b' AND value = 'bar'` (obviously two different records). I think the only way to do it is with a join.

  Kerry : Heh - last answer was perfect. Sorry for the confusion.
  From OMG Ponies