mysql query with AND, OR and NOT

Question

Lets say I have a table of articles with as many to many relationship with topics. Each topic assigned to an article has a type field which can contain 1 of 3 values AND, NOT, and OR.

Articles
   id
   ....

Topics
   id
   ....

ArticleTopics
   article_id
   topic_id
   type

I want to create a query that says returns all articles that have:

ALL of the following topics: 1, 2, 3 (AND association)
   AND
ANY of the following topics: 4, 5, 6 (OR association)
   AND
NONE of the following topics 7, 8 (NOT association)

How do I go about creating this query?

Thanks in advance!

Answer 1

The ALL and NOT parts are very simple, you just chain them with ANDs:

SELECT X FROM Y WHERE a AND b AND c AND NOT d AND e AND NOT e.

And the ORs go between:

SELECT X FROM Y WHERE ((a AND b AND c) AND (d OR e OR f)) AND NOT g AND NOT h

replace small numbers with comparisons and you're done. So if you want to do this in code, sort your conditions and then just chain them together as a String. Be careful to avoid SQL-Insertions.