Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

SELECT DISTINCT value FROM postmeta
WHERE post_id = {an array of post_id's}

something to that extent.

share|improve this question
    
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

1 Answer 1

up vote 3 down vote accepted

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

SELECT DISTINCT p1.value
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';
share|improve this answer
    
Thanks, works like a champ! –  Jerry Tunin Nov 26 '12 at 21:12
    
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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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