Retrieving records that has both tag T1 and T2

Hi all,

I need an sql query that will retrieve all items that have both tags, not any of tags. I already have a query but returns all items that hove both tags, not expected result. Find detailed description below. Thanks!

Table structure:

ITEMS TABLE
------------
item_id
item_name

TAGS TABLE
----------
tag_id
tag_name

ITEMTAGS TABLE
---------------
tag_id
item_id

Query:

SELECT Items.* FROM Items 
INNER JOIN ItemTags ON Items.item_id = ItemTags.item_id
WHERE ItemTags.tag_id IN (T1, T2)
GROUP BY Items.item_id

Result: All items that have T1 or T2

Expected result: All items that have both T1 and T2

From stackoverflow

• If your database supports the intersect keyword (SqlServer does) you can write:

  SELECT Items.* 
  FROM Items 
  WHERE Items.item_id in 
  /* intersection between items that have the tag T1 
     and the ones that have the tag T2 */ 
  (
      SELECT item_id FROM ItemTags WHERE tag_id = T1
      INTERSECT 
      SELECT item_id FROM ItemTags WHERE tag_id = T2
  )
  

  If not, you will need to do something like:

  SELECT Items.* 
  FROM Items 
  WHERE Items.item_id in 
  (
      SELECT ItemTags1.item_id 
      FROM ItemTags as ItemTags1 
          INNER JOIN ItemTags as ItemTags2 
          ON ItemTags1.item_id = ItemTags2.item_id 
      WHERE 
              ItemTags1.tag_id = T1
          AND ItemTags2.tag_id = T2
  )
  

  On second query take care of possible performance problems because of ItemTags1.item_id = ItemTags2.item_id . You will need an index on the foreign key to work well.

  matte : mysql doesn't support this as i know

  Aleris : @Mert: well when I've posted it was tagged 'sql' not 'mysql' :)

• select i.*
  from items i, itemtags it1, itemtags it2
  where i.item_id=it1.item_id and it1.tag_id=T1
  and i.item_id=it2.item_id and it2.tag_id=T2;