SQL Query: Selecting a row if it has all related parameters in a linked table

Question

taking into account the definition of the table:

Articles: art_id | name -------|-------------- 1 | article1 2 | article2 3 | article3 Tags: tag_id | description -------|-------------- 1 | Scientific 2 | Long 3 | Short article_tags: art_id | tag_id -------|--------- 1 | 1 1 | 2 2 | 1 2 | 3 3 | 1 3 | 2 3 | 3 

Question: How to choose all articles that are OBJECTIVE Scientific and Short ?

Please note that this should be common for tag combinations [2..N] ...

thanks for the help.

Answer 1

You can use the following query to get the result:

 select a.art_id, a.name from articles a inner join article_tags at on a.art_id = at.art_id inner join tags t on at.tag_id = t.tag_id where t.description in ('Short', 'Scientific') -- tags here group by a.art_id, a.name having count(distinct t.tag_id) = 2 -- total count of tags here 

See SQL Fiddle with Demo

Or it could be written:

 select a.art_id, a.name from articles a inner join article_tags at on a.art_id = at.art_id inner join tags t on at.tag_id = t.tag_id group by a.art_id, a.name having sum(case when t.description = 'Short' then 1 else 0 end) >= 1 and sum(case when t.description = 'Scientific' then 1 else 0 end) >= ; 

See SQL Fiddle with Demo .

If you just want to return the article id, you can simply query the article_tag table:

 select art_id from article_tags where tag_id in (1, 3) group by art_id having count(distinct tag_id) = 2 

See SQL Fiddle with Demo

Answer 2

 SELECT * FROM articles WHERE art_id IN ( SELECT art_id FROM article_tags GROUP BY art_id HAVING COUNT(art_id) > 1 )