mysql complex joins

This is relating to mysql query with AND, OR and NOT and also mysql has all values I 开发者_StackOverflow中文版have solved the AND part of the problem, but Now I also now need to solve the OR and the NOT.

Just to confirm the below statement gives me all articles that have each topic 1, 2 and 3

SELECT x.*
FROM Article x INNER JOIN
(SELECT t.article_id, COUNT(t.article_id)
  FROM articleTopics t
  WHERE t.topic_id IN ('1','2','3')
  GROUP BY t.article_id
  HAVING COUNT(t.article_id)>=3
  ORDER BY COUNT(t.article_id) DESC
  LIMIT 0,100) AS ilv
ON x.id=ilv.article_id

What I am trying to do is add something to the query that would exclude all articles that are also associated with topics 4 and 5. : NOT

I will also want to add the articles that have topics 6, or 7 as long as they match the previous constraints.

E.g.

SELECT all Articles where the articles has
    all the following topics (1,2,3) #AND
AND
    none of the following topics (4,5) #NOT
AND
    may have any of the following topics(6,7) #OR

I hope that makes sense!

---

I would use the EXISTS function like:

SELECT ...
FROM Article a
WHERE EXISTS (SELECT topic_id FROM articleTopics t WHERE t.article_id = a.article_id AND topic_id = 1)
  AND EXISTS (SELECT topic_id FROM articleTopics t WHERE t.article_id = a.article_id AND topic_id = 2)
  AND EXISTS (SELECT topic_id FROM articleTopics t WHERE t.article_id = a.article_id AND topic_id = 3)
  AND NOT EXISTS (SELECT topic_id FROM articleTopics t WHERE t.article_id = a.article_id AND topic_id = 4)
  AND NOT EXISTS (SELECT topic_id FROM articleTopics t WHERE t.article_id = a.article_id AND topic_id = 5)
  AND (EXISTS (SELECT topic_id FROM articleTopics t WHERE t.article_id = a.article_id AND topic_id = 6)
    OR EXISTS (SELECT topic_id FROM articleTopics t WHERE t.article_id = a.article_id AND topic_id = 7))

---

Seems like it would be much simpler to use a regular JOIN than to use a subselect. Should be faster too.

SELECT * FROM articles JOIN articleTopics USING(article_id) 
 WHERE topic_id IN(1,2,3) AND topic_id NOT IN (4,5) GROUP BY article_id;

I don't see why you need to specify 6 and 7. Unless you're saying if it has 6 or 7 the other rules don't matter. In which case you could do:

SELECT * FROM articles JOIN articleTopics USING(article_id) 
  WHERE (topic_id IN(1,2,3) AND topic_id NOT IN (4,5)) OR topic_id IN(6,7) GROUP BY article_id;

---

This is what I ended up with:

SELECT x.*
FROM Article x INNER JOIN
(SELECT t.article_id, COUNT(t.article_id)
  FROM articleTopics t
  WHERE t.topic_id IN ('1','2','3')
  AND NOT EXISTS (
       SELECT 1
       FROM articleTopics
       WHERE article_id = t.article_id
       AND entity_id IN ('4','5'))
  GROUP BY t.article_id
  HAVING COUNT(t.article_id)>=3
  ORDER BY COUNT(t.article_id) DESC
  LIMIT 0,100) AS ilv
ON x.id=ilv.article_id
LEFT JOIN articleTopics at ON at.article_id = x.id
WHERE ae_topic_id IN ('6','7')

This says all articles must contain all topics 1,2,3 and atleast 1 from 6 or 7 but never include 4 or 5

Thanks for your help on this.