SQL select rows having several values in common

mysql, sql

Solution

This problem is called `Relational Division`

SELECT  a.*
FROM    articles a
        INNER JOIN
        (
            SELECT at.article_id 
            FROM   articles a
                   INNER JOIN articles_tags at
                      ON a.id = at.article_id
            WHERE  at.tag_id IN(2, 3)
            GROUP  BY at.article_id
            HAVING COUNT(*) = 2
        ) b ON a.id = b.article_id

- SQL of Relational Division

Problem

I'm having a problem to select some articles rows depending on a condition. Here's my problem : All my articles can have several 'tags' attached, so my structure looks like this : ``` articles articles_tags tags ¯¯¯¯¯¯¯¯ ¯¯¯¯¯¯¯¯¯¯¯¯¯ ¯¯¯¯ id article_id id title tag_id name content [...] ``` Now I'd like to select ALL articles which have BOTH tags 2 and 3 for example. I tried this : ``` SELECT * FROM articles a JOIN articles_tags at ON (a.id = at.article_id) WHERE at.tag_id IN(2, 3) GROUP BY article_id ``` But this will select all articles which have AT LEAST tags IDs #2 or #3 (seems logic after all) Is there any trick or something to get only the articles having a defined list of tag IDs ? Thanks you

Original source