This can be a very specific question regarding mysql as implemented in WordPress.

I am attempting to create a wordpress plugin that will show (choose) posts which have specific 'tags' and fit in with specific 'groups' (both multiple)

I had been told no one is able since the way groups and tags are saved:

  1. wordpress_posts consists of a lists of publish, each posts come with an "ID"
  2. wordpress_terms consists of a listing of terms (both groups and tags). Eac term includes a TERM_ID
  3. wordpress_term_taxonomy has a listing of terms using their TERM_IDs and it has a Taxonomy definition for every one of individuals (whether Category or perhaps a Tag)
  4. wordpress_term_relationship has associations betweens terms and posts

How do i join the tables to obtain all posts with tags "Nuclear" and "Deals" which fit in with the course "Category1" ?

Thanks!

I misinterpreted you. I figured you desired Nuclear or Deals. The below should provide you with only Nuclear and Deals.


choose p.* from wordpress_posts p, wordpress_terms t, wordpress_term_taxonomy tt, wordpress_term_relationship tr, wordpress_terms t2, wordpress_term_taxonomy tt2, wordpress_term_relationship tr2 wordpress_terms t2, wordpress_term_taxonomy tt2, wordpress_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.title = 'Category1') and (tt2.taxonomy = 'post_tag' and tt2.term_id = t2.term_id and t2.title = 'Nuclear') and (tt3.taxonomy = 'post_tag' and tt3.term_id = t3.term_id and t3.title = 'Deals')

Exactly what a gross DB structure.

Anyway, I'd make a move such as this (note I favor Is available to joins, however, you can re-write them as joins if you want most query analyzers will collapse these to exactly the same query plan anyway). You might want to perform some additional juggling some way to really make it work...

SELECT *
  FROM wp_posts p
 WHERE EXISTS( SELECT *
                 FROM wp_term_relationship tr
                WHERE tr.object_id = p.id
                  AND EXISTS( SELECT *
                                FROM wp_term_taxonomy tt
                               WHERE tt.term_taxonomy_id = tr.term_taxonomy_id
                                 AND tt.taxonomy         = 'category'
                                 AND EXISTS( SELECT *
                                               FROM wp_terms t
                                              WHERE t.term_id = tt.term_id
                                                AND t.name    = "Category1" 
                                           )
                            )
                  AND EXISTS( SELECT *
                                FROM wp_term_taxonomy tt
                               WHERE tt.term_taxonomy_id = tr.term_taxonomy_id
                                 AND tt.taxonomy         = 'post_tag'
                                 AND EXISTS( SELECT *
                                               FROM wp_terms t
                                              WHERE t.term_id = tt.term_id
                                                AND t.name    = "Nuclear" 
                                           )
                                 AND EXISTS( SELECT *
                                               FROM wp_terms t
                                              WHERE t.term_id = tt.term_id
                                                AND t.name    = "Deals" 
                                           )
                            )
            )