Getting multiple values in multiple rows from multiple tables in MySQL

Question

I'm using WordPress. I want to get everything from the wp_posts table and two values from the postmeta table.

The "wp_postmeta" has the columns "post_id", "meta_key", and "meta_value".

So far, I'm able to get one of the "meta_value":

SELECT wp_post.*, $wpdb->postmeta.meta_value AS votes 
FROM wp_posts 
INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) 
WHERE post_type='post' 
AND wp_postmeta.meta_key = 'votes'

However, I also want to get another "meta_value" with the same "post_id" but a different "meta_key". How can I extend this query to the "meta_value"?

Answer 1

If you don't want multiple rows per post, you can do this by having multiple joins:

SELECT wp_post.*, metavotes.meta_value AS votes, metaother.meta_value AS other
FROM wp_posts 
INNER JOIN wp_postmeta metavotes 
    ON ( wp_posts.ID = wp_postmeta.post_id AND wp_postmeta.meta_key = 'votes') 
INNER JOIN wp_postmeta metaother
    ON ( wp_posts.ID = wp_postmeta.post_id AND wp_postmeta.meta_key = 'other') 
WHERE post_type='post' 

(this assumes there is always exactly 1 row for each piece of metadata in wp_postmeta. I'm not familiar enough with wordpress to know whether this is the case. If metadata is optional, use LEFT OUTER JOIN's instead. If you can have multiple, what kind of output do you want?)

Answer 2

How about adding your additional meta_key to the query with the IN clause. Let's say it's the string value foo that you want to add:

SELECT wp_post.*, $wpdb->postmeta.meta_value AS votes 
FROM wp_posts 
INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) 
WHERE post_type='post' 
AND wp_postmeta.meta_key IN ('votes', 'foo');

Answer 3

Maybe try something like this:

SELECT wp_posts.*, wp_postmeta.meta_value, wp_postmeta.meta_key
       FROM wp_posts INNER JOIN wp_postmeta ON(wp_posts.ID = wp_postmeta.post_id)
       WHERE wp_posts.post_type='post'
             AND wp_postmeta.meta_key IN ('votes', ...)