mySQL query key value pairs

Question

I am doing a query on a wordpress table (postmeta). The table has keys and values and I need a query that will get all rows that match "key1" equal to "value1" and "key2" equal to "value2" ordered by value2

The table basically has an id, postid, key and value columns.

I am not sure even where to start. I can find one value fine ie ... where key='featured' & value=true. But I need the top 25 ordered by the value of the rows where key='hits' meaning I need the value of the corresponding hits key for those featured rows

I am not sure how to do this.

TIA

Answer 1

It is difficult to say exactly how to do this with the limited details that you provided. But when you want to return key/value pairs you can use the following.

You can join on your table multiple times:

select p1.postid,
  p1.value Featured,
  p2.value Hits
from postmeta p1
left join postmeta p2
  on p1.postid = p2.postid
  and p2.key = 'hits'
where p1.key ='featured';

See SQL Fiddle with Demo

Or you can use an aggregate function with a CASE expression (using sum() assumes a numeric value, you can use max()/min() for string values:

select postid,
  sum(case when `key` = 'featured' then value end) Featured,
  sum(case when `key` = 'hits' then value end) Hits
from postmeta
group by postid

See SQL Fiddle with Demo