Return all columns belonging to a record in a key value table when matching multiple values

Question

Hi

I'm trying to do what seems simple, but my SQL skills are limited :)

I have one table in MySQL containing key value pairs like this:

 ID    |  Record_ID  |    Key    |  Value
  1    |       1     |   type    |  person
  2    |       1     |   name    |  Henrik
  3    |       1     |  skills   |  Not any sql skills
  4    |       1     |   date    |  Today
 ...
 100   |      12     |   type    |  something else
 101   |      12     |  format   |  text
 102   |      13     |   type    |  color
 103   |      13     |   size    |  127

I would like to perform a search that matches something like:

key=type, value=person AND key=name, value like Henrik% AND key=skills, value LIKE %sql%

And all rows belonging to Record_ID = 1 should be returned.

Please note: each record types has a fixed number of key value pairs, e.g. the person record always has 4 pairs. When the user performs a search: type is always set, but the other values may or may not be included in the search condition.

Answer 1

Using a self join:

SELECT DISTINCT t.*
   FROM TABLE t
   JOIN TABLE t2 ON t2.record_id = t.record_id
                     AND (    (t2.key = 'type' AND t2.value = 'person')
                              OR (t2.key = 'name' AND t2.value LIKE 'Henrik%')
                              OR (t2.key = 'skills' AND t2.value LIKE '%sql%')  )

Using IN:

SELECT t.*
   FROM TABLE t
  WHERE t.record_id IN (SELECT x.record_id
                                    FROM TABLE x
                                   WHERE (    (t2.key = 'type' AND t2.value = 'person')
                              OR (t2.key = 'name' AND t2.value LIKE 'Henrik%')
                              OR (t2.key = 'skills' AND t2.value LIKE '%sql%') )