Given a table definition:
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
The question is How to select all articles that are BOTH Scientific and Short?
Please note, it should be general for [2.N) tag combinations...
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
Or this 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, then you could just 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