Re: SQL question: find items tagged with specific tags

[Sebastian Mendel]

Ingo Weiss schrieb:
> Thanks, Sebastian!
> 
> I have tried this one before. The problem is that it finds all items the 
> tags of which include EITHER 'blue' OR 'red', not 'blue' AND 'red':

oh ... "and" ..., i missred

      SELECT DISTINCT items.*
        FROM items
  INNER JOIN taggings
          ON items.id = taggings.item_id
  INNER JOIN tags
          ON tags.id = taggings.tag_id
         AND tags.name = 'blue'
         AND tags.name = 'red';

or

SELECT DISTINCT items.*
        COUNT(items.id)
   FROM [your join above]
  WHERE tags.name IN ('blue', 'red')
HAVING COUNT(items.id) = 2;

-- 
Sebastian Mendel