MYSQL Query Joining Tables

1 Views

Here is my query as is for a WP database. 

$letters = $wpdb->get_col(
"SELECT DISTINCT LEFT(post_title,1) AS first_letter FROM $wpdb->posts
WHERE post_type = '$post_type' AND post_status = 'publish'
ORDER BY first_letter ASC"
);

It is a baby name database and I want to make a widget to separate male/female so I need to do a query like above but to get posts with only certain postmeta which below is the key and value I am looking for.

Read More

Key – spin2

Value – Male

How do I add this into the query since it is in a different table?

Thanks!

Post Views: 1

Related posts

Leave a Reply

1 comment

  1. You just need to use an INNER JOIN:

    SELECT DISTINCT LEFT(posts.post_title,1) AS first_letter 
FROM $wpdb->posts AS posts 
    INNER JOIN $wpdb->postmeta AS meta
        ON posts.ID = meta.post_id
WHERE posts.post_type = '$post_type' 
    AND posts.post_status = 'publish'
    AND meta.meta_key = 'spin2'
    AND meta.meta_value = 'Male'
ORDER BY first_letter ASC