Wordpress - MySQL Query to Retrieve Category from wp_posts

Figured it out. @belinus is probably the solution for you if you're looking to do this within WordPress.

As for just a raw SQL query you can play around with, I found this one, modified it a little bit and it returns all the products for a particular category.

There are three tables required to do this wp_posts wp_term_relationships and wp_term_taxonomy

SELECT *
FROM wp_posts
LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
LEFT JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
WHERE wp_term_taxonomy.term_id IN (307)
GROUP BY wp_posts.ID

Just replace 307 with the term_id of the category you're looking to get results for. You can find this by searching in the wp_terms name column and it will return the term id associated.

You can also return multiple categories if you wish, just comma seperate them in the WHERE clause for example WHERE wp_term_taxonomy.term_id IN (307, 450, 200, 99).