Selecting a row if it has all related options in a related table

Question

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...

Please replace your **sql** tag with one specific to the RDBMS you're using (MySQL, SQL-Server, Oracle, etc.). The best answer depends on which version you're using. — Barmar, Jun 24 '13 at 15:12
possible duplicate of [Select all rows that have at least a list of features](http://stackoverflow.com/questions/13889547/select-all-rows-that-have-at-least-a-list-of-features) — Barmar, Jun 24 '13 at 15:15

Taryn · Accepted Answer · 2013-06-24T15:13:37.403

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 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

See SQL Fiddle with Demo

score 1 · Answer 2 · edited Feb 08 '14 at 04:40

1

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

edited Feb 08 '14 at 04:40

Code Maverick

20,171
12
62
114

answered Jun 24 '13 at 15:11

jksloan

184
1
4

Selecting a row if it has all related options in a related table

2 Answers2