John Davidson

Message:

I can get it working for posts that match a title and are in a category, however I cannot get posts that match a title and are NOT in a category

select * 

from wp_posts 

join wp_term_relationships on (wp_posts.ID = wp_term_relationships.object_id)

 where (wp_term_relationships.term_taxonomy_id NOT in (107))

 and (post_title REGEXP 'video|film' ) 

 and (post_type = 'post' OR post_type = 'xdays1')

 GROUP BY wp_posts.ID

It has the same amount of results as this, without any category code:

select * 

from wp_posts 

WHERE

(post_title REGEXP 'video|film' ) 

 and (post_type = 'post' OR post_type = 'xdays1')

 GROUP BY wp_posts.ID

I assume my syntax is wrong...

I have about 200 posts in category with id 107. So I want results to not include those.

help appreciated!

---

My Answer:
You can achieve this by using a subquery to select the posts that are in the category you want to exclude, and then use a NOT IN clause to exclude those posts from your main query. Here's an example:

sql
SELECT *
FROM wp_posts
WHERE post_title REGEXP 'video|film'
AND post_type IN ('post', 'xdays1')
AND ID NOT IN (
SELECT object_id
FROM wp_term_relationships
WHERE term_taxonomy_id = 107
)
GROUP BY ID


This query will select all posts with titles that match the regex 'video|film' and are of type 'post' or 'xdays1', but exclude any posts that are in the category with ID 107.