How to select posts with specific tags/categories in WordPress

Question

This is a very specific question regarding MySQL as implemented in WordPress.

I'm trying to develop a plugin that will show (select) posts that have specific 'tags' and belong to specific 'categories' (both multiple)

I was told it's impossible because of the way categories and tags are stored:

1. wp_posts contains a list of posts, each post have an "ID"
2. wp_terms contains a list of terms (both categories and tags). Each term has a TERM_ID
3. wp_term_taxonomy has a list of terms with their TERM_IDs and has a Taxonomy definition for each one of those (either a Category or a Tag)
4. wp_term_relationships has associations between terms and posts

How can I join the tables to get all posts with tags "Nuclear" and "Deals" that also belong to the category "Category1"?

Answer 1

I misunderstood you. I thought you wanted Nuclear or Deals. The below should give you only Nuclear and Deals.

select p.*
from wp_posts p, wp_terms t, wp_term_taxonomy tt, wp_term_relationship tr,
wp_terms t2, wp_term_taxonomy tt2, wp_term_relationship tr2
wp_terms t2, wp_term_taxonomy tt2, wp_term_relationship tr2

where p.id = tr.object_id and t.term_id = tt.term_id and tr.term_taxonomy_id = tt.term_taxonomy_id

and p.id = tr2.object_id and t2.term_id = tt2.term_id and tr2.term_taxonomy_id = tt2.term_taxonomy_id

and p.id = tr3.object_id and t3.term_id = tt3.term_id and tr3.term_taxonomy_id = tt3.term_taxonomy_id

and (tt.taxonomy = 'category' and tt.term_id = t.term_id and t.name = 'Category1')
and (tt2.taxonomy = 'post_tag' and tt2.term_id = t2.term_id and t2.name = 'Nuclear')
and (tt3.taxonomy = 'post_tag' and tt3.term_id = t3.term_id and t3.name = 'Deals')