Selecting where more than one value from a joined table matches a given set

I'm using Postgresql. I have a table of items, a table of tags, and a table that links many tags to an item. I need to select items that match 2 (or more) given tags.

I can select items that match 1 or more with WHERE tag.name IN ('tag1', 'tag2') In MySQL I think I would have been able to say WHERE x IN ALL (y, z), but that doesn't work is pgsql it seems.

So far the best I have is joining the two tables twice using a sub-query. This obviously won't scale to matching on many tags and I'm sure isn't the most efficient approach.

SELECT * 
FROM item 
JOIN (SELECT item.id FROM item 
      JOIN tagged on tagged.item=item.id 
      JOIN tag ON tag.id=taged.tag 
      WHERE tag.name='tagOne') p ON p.id=item.id
JOIN tagged ON tagged.item=item.id
JOIN tag ON tag.id=tagged.tag
WHERE tag.name='tagTwo'`

Edit: I'm still testing things out but using this in a sub-query works better than my above attempt

SELECT item.id, count(tag2) AS开发者_如何学JAVA relevance
FROM item
JOIN tagged tagged1 ON tagged1.item=item.id
JOIN tag tag1 ON (tag1.id=tagged1.tag AND tag1.name='tag1')
JOIN tagged tagged2 ON tagged2.item=item.id
JOIN tag tag2 ON (tag2.id=tagged2.tag)
WHERE tag2.name IN ('tag2', 'tag3')
GROUP BY item.id

As requested here's some table definitions for clarification:

CREATE TABLE item (id serial, [...]);
CREATE TABLE tag (id serial, name string UNIQUE);
CREATE TABLE taged (tag int references tag(id), item int references item(id));

---

here are 3 (of many) possible approaches:

select * 
from item 
where id in ( select tagged.item
              from tagged join tag on(tag.id=taged.tag)
              where tag.name in('tagOne', 'tagTwo')
              group by tagged.item
              having count(*)=2 );

select * 
from item join ( select tagged.item, count(*) as numtags 
                 from tagged join tag on(tag.id=taged.tag)
                 where tag.name in('tagOne', 'tagTwo')
                 group by tagged.item ) using (id)
where numtags=2;

select *
from item
where id in ( select tagged.item
              from tagged join tag on(tag.id=taged.tag)
              where tag.name='tagOne'
              intersect
              select tagged.item
              from tagged join tag on(tag.id=taged.tag)
              where tag.name='tagTwo' );

if you just want 2 or more matches but you don't mind which tags are matched:

select * 
from item 
where id in ( select item
              from tagged
              group by item
              having count(*)>=2 );

---

I'm not sure I've understood, but maybe you can simply write:

WHERE tag.name IN (y) AND tag.name IN (z)

---

I like to use the form:

SELECT *
FROM item
WHERE EXISTS (SELECT 1 FROM tagged JOIN tag ON tag.id = tagged.tag
              WHERE tag.name = 'tag1')
      AND
      EXISTS (SELECT 1 FROM tagged JOIN tag ON tag.id = tagged.tag
              WHERE tag.name = 'tag2')

You assert that "this obviously won't scale to matching on many tags and I'm sure isn't the most efficient approach" -- but it sounds like you're guessing?