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

What have you tried yourself? Can you also show us the table definition of postmeta? I recommend using http://sqlfiddle.com/ to give an example of the database and your query. — Wolph, Feb 12 '13 at 01:52

Taryn · Answer 1 · 2013-02-12T02:34:29.260

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

mySQL query key value pairs

1 Answers1

Linked