mysql has all values

This is relating to my last question mysql query with AND, OR and NOT

Instead of editing the question, I am asking a new one because the question is only part of the previous question with an alteration.

I am looking to do a mysql query that returns me all articles that have all required topics.

Article
   id
   ....

Topic
   id
   ....

ArticleTopics
   article_id
   topic_id
   type

something that would effectively do:

SELECT * FROM Article LEFT JOIN ArticleTopics ON Article.id = ArticleTopics.article_id
WHERE ArticleTopics.topic_id HAS ALL (these topics)

Is this开发者_运维百科 possible? What is the best approach for this?

Several of the other answers suggest using aliases on the child table for each filter clause - this may not be very efficient or scale well.

Consider:

SELECT x.*
FROM Article x INNER JOIN
(SELECT t.article_id, COUNT(t.article_id)
  FROM articleTopics t
  WHERE t.topic_id IN ([your_list_of_topics])
  GROUP BY t.article_id
  HAVING COUNT(t.article_id)>=[number of elements in [your_list_of_topics]]
  ORDER BY COUNT(t.article_id) DESC
  LIMIT 0,100) AS ilv
ON x.id=ilv.article_id

Another advantage of this approach is that the structure of the query doesn't need to change with the number of topics you are searching for - you could even put them in a temporary table and perform a join instead of using the ' IN (...)' literal.

You'd need to try it out to see which query behaves better.

That is done using multiple joins with the same table.

To select all articles that have topics with ID 1, 2 and 3, you need to do:

SELECT * FROM Article a
  INNER JOIN ArticleTopics at1 ON a.id = at1.article_id AND at1.topic_id = 1
  INNER JOIN ArticleTopics at2 ON a.id = at2.article_id AND at2.topic_id = 2
  INNER JOIN ArticleTopics at3 ON a.id = at3.article_id AND at3.topic_id = 3

// EDIT Fixed it. Added table aliasses; I must have been working with good ORM solutions for too long...

SELECT
    *
FROM
    Article
WHERE
    (SELECT COUNT(*) FROM ArticleTopics
        WHERE Article.id = ArticleTopics.article_id AND
        ArticleTopics.topic_id=1) > 0 AND
    (SELECT COUNT(*) FROM ArticleTopics
        WHERE Article.id = ArticleTopics.article_id AND
        ArticleTopics.topic_id=2) > 0 AND
    (SELECT COUNT(*) FROM ArticleTopics
        WHERE Article.id = ArticleTopics.article_id AND
        ArticleTopics.topic_id=3) > 0 AND
    ...