SQL Query INNER JOIN or LEFT JOIN Want to select posts from specific categories in Wordpress but do NOT select those of another specific category

Question

Selecting specific categories works fine:

SELECT ID, post_content, post_title
FROM wp_posts
  LEFT JOIN wp_term_relationships ON wp_term_relationships.object_id = wp_posts.id
  AND wp_term_relationships.term_taxonomy_id IN (17,19)

Now adding the exception, to NOT select posts which are for example in category 17 AND in category 20, they should be excluded from the select query.

I tried this:

SELECT ID, post_content, post_title
FROM wp_posts
  LEFT JOIN wp_term_relationships ON wp_term_relationships.object_id = wp_posts.id AND wp_term_relationships.term_taxonomy_id IN (17,19)
WHERE wp_term_relationships.term_taxonomy_id NOT IN (20)

But it does not show any effect. Anyone got an idea what I am doing wrong?

Thank you in advance.

Answer 1

I think you want:

SELECT wp.ID, wp.post_content, wp.post_title
FROM wp_posts wp
WHERE NOT EXISTS (SELECT 1
                  FROM  wp_term_relationships tr 
                  WHERE tr.object_id = wp.id AND
                        tr.term_taxonomy_id IN (17, 19)
                 );

This removes any relationships that have either 17 or 19.

Another approach is to use aggregation:

SELECT wp.ID, wp.post_content, wp.post_title
FROM wp_posts wp JOIN
     wp_term_relationships tr 
     ON tr.object_id = wp.id
GROUP BY wp.ID, wp.post_content, wp.post_title
HAVING SUM( CASE WHEN tr.term_taxonomy_id IN (17, 19) THEN 1 ELSE 0 END) = 0;