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
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...