MYSQL Query Joining Tables

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.

Key - spin2

Value - Male

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

Thanks!

Solutions

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
Tags: Mysql / SQL / Wordpress

Similar questions

WP - troubles with joining two tables
i was trying to find at the internet the answer, and I found something, but i do not get it :-) I have just a two tables - wp_users & wp_usermeta. These two tables has the same ID (wp_users.ID and wp_usermeta.user_id). All I want is to take email address from wp_users and join it with nickname from wp_usermeta. I am beginner in sql and this wou...
Joining two tables twice with different rows selected each time
I have to join wp_posts with wp_postsmeta twice to get the rows. First with the post_type=product and next with the post_type=attachment. wp_posts: wp_postsmeta Query: Next, I also want to join wp_posts with wp_postsmeta again with post_type = attachment and meta_key =_thumbnail wp_postsmeta: Then I have use the meta_value of the result and join th...
SQL- Joining tables and checking if something exists not working/incorrect syntax
I am trying to write an sql statement in phpmyadmin. I have three tables - one tables has all of the data from my posts with a unique ID of wp_posts.ID. The other table is a tags table that has a list of tags that can be added to posts. Each tag has a unique tag id called term_taxonomy_id. The last table is a relationship table that only creates a ...
Wordpress/MySQL (InnoDB) tables get fragmented near instantaneously after optimizing tables?
So I originally ran mysqltuner and it kept saying (on a fresh Wordpress install, using Percona InnoDB with random dummy data for Wordpress posts) that there was fragmented tables. I don't know if this is the proper way to check for fragmented tables or not: but that spits out: So I'm unsure if those tables are truly fragmented or not. I've ran: whi...
Need help with MySQL, joining two queries
I'm running WordPress and I'd like to write a custom query that would show a monthly archive of posts based on one specific category. I used the debug mode to find out how to get a monthly archive based on all posts, select a specific category. Here are the queries: Get montlhy archive: Select category: I can't figure out how to join these two ;) A...
Slow query when joining wp_posts with a lookup-table?
I'm developing a map-based website and need to get posts associated with a place. Each place has a post in wp_posts and the other associated posts (images of each place etc) is associated with the place using the custom lookup-table wp_lookup_places by post_id. How can I query to get the results in ms? I thought it would be a simple task for a mysq...

Also ask

We use cookies to deliver the best possible experience on our website. By continuing to use this site, accepting or closing this box, you consent to our use of cookies. To learn more, visit our privacy policy.