CODEWITHSUNDEEP
phpsqlwordpress
ostrichegret
ostrichegret

Reputation: 189

How to get multiple value from different condition, Just like WP Meta Query works?

META TABLE ==
meta_id | post_id | meta_key | meta_value
1       | 101     | quantity | 8
2       | 101     | price    | 100
3       | 102     | quantity | 7
4       | 102     | price    | 56
5       | 103     | quantity | 12
6       | 103     | price    | 256


POST TABLE ==
post_id | name      | about
101     | Pencil    | Luxurious pencil only for you
102     | Eraser    | All your mistakes, gone!
103     | Pen       | Unrivaled penmanship, stronger than sword.

How to do a query to post id and get post : name,about with meta: value, price and quantity in a single go?

so the results will be

 101   | Pencil   | Luxurious pencil only for you              | 8   | 100
 102   | Eraser   | All your mistakes, gone!                   | 7   | 56
 103   | Pen      | Unrivaled penmanship, stronger than sword. | 12  | 256

Thank you

Upvotes: 0

Views: 62

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

You have to join meta_table twice. Use left join if either quantity or price will not have an entry in meta_table for a given post_id.

select p.post_id, p.name, p.about, m1.meta_value, m2.meta_value
from post_table p
join meta_table m1 on m1.post_id = p.post_id and m1.meta_key = 'quantity'
join meta_table m2 on m2.post_id = p.post_id and m2.meta_key = 'price'

Upvotes: 1

Related Questions