How to select posts from particular categories in WordPress database

Question

I want to select posts from some specific categories from my WordPress database. I do not see any column for category in the wp_posts table. How can I select post from some specific categories?

Answer 1

In WordPress, posts are stored in the wp_posts table, and their category relationships are managed through the wp_terms, wp_term_taxonomy, and wp_term_relationships tables. To select posts from particular categories, you will need to join these tables. The following SQL query will select post_name and post_title of the published posts from categories with category_id 1 and 2.

SELECT DISTINCT a.post_name, a.post_title
FROM wp_posts a
INNER JOIN wp_posts b ON a.id = b.post_parent
INNER JOIN wp_term_relationships t ON a.ID = t.object_id
WHERE a.post_status = 'publish'
AND b.id = a.id +1
AND t.term_taxonomy_id IN (1, 2)