tagsrelationsql

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


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


Solution

  • 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