I've got table like:
id | meta_key | meta_value ------------- 1 | color | red 1 | price | 10 2 | color | red 2 | price | 20 3 | color | blue 3 | price | 10 how can I get all unique ids were color=red and price=10 . Can I get it with single simple query, w/o pivots etc.
I like to approach these using group by and having:
select id from t where (meta_key = 'color' and meta_value = 'red') or (meta_key = 'price' and meta_value = '10') group by id having count(distinct meta_key) = 2; An alternative is a join. If there a no duplicate values for an id:
select id from t tc join t tp on tc.id = tp.id and tc.meta_key = 'color' and tc.meta_value = 'red' and tp.meta_key = 'price' and tp.meta_value = '10'; The group by method has the advantage of scalability and expressibility. It is easy to express many conditions (color is not red, manufactured in US or China) that are not simple equality. Also, additional conditions have very similar performance.
The second probably performs better (with the right indexes) on a couple of conditions.
group by works fine for me. Thanks a lot.