Basically I have 2 tables: post_meta and posts.

post_meta has a post_id, key and value:

post_id  key      value
6343     make     Chevy
6343     model    Volt
6794     make     Chevy
6794     model    Silverado

posts has a post_id and content:

post_id  content
5434     Hello World!
6343     Chevy Volt review
6794     The New Silverado

What I need to do is pull the model based on the make (pull all unique models with the make "Chevy"). The database isn't very big so it doesn't need to be a real fast query. Basically the plan in my mind was to get the model of every post_id that has the post_meta.make "Chevy".

SELECT post_id FROM postmeta
WHERE key = 'make'
AND value = 'Chevy'

then take the post_id

WHERE post_id = {an array of post_id's}

something to that extent.

  • It doesn't seem like you need to store both the make and model of a vehicle for a post, since the model sort of indicates already what the make is, doesn't it? I mean, if 'model' is 'Silverado', then make is only ever going to be 'Chevy", no? Never Ford or Toyota. But that's a different discussion. – mdoyle Nov 26 '12 at 20:57

Given your table structure, this should extract what you are looking for:

FROM postmeta p1
INNER JOIN postmeta p2 ON p1.post_id = p2.post_id
WHERE p2.key = 'make'
    AND p2.value = 'Chevy'
    AND p1.key = 'model';
| improve this answer | |
  • and you don't need the DISTINCT if the (post_id, key) combination is unique. – ypercubeᵀᴹ Nov 27 '12 at 10:10
  • Agreed, I did take that out as well. – Jerry Tunin Nov 27 '12 at 13:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.