Can't figure out this query..

[Nimlhûg]

Howdy,

was wonderin' if someone could help me write this query here, because I'm a bit stuck. Been racking my brain over it for a while now, and I haven't been able to come up with a solution. Any help would be much appreciated.

Here goes:

I have a table, called TagPairs

It has two fields:
TagID
ItemID

Now what I need to be able to do (you know how tags work), is select a list of items from that table, which match all of the given tag ids. The ItemID should be unique (distinct), and it's of course not an issue if the item has more tags that aren't limited. If that makes sense ..

Thanks guys

[guelphdad]

show some sample data from your table and then sample rows of output you are looking for.

[Nimlhûg]

Sure. Some sample data:

ItemID: 1
TagID: 2

ItemID: 1
TagID: 11

ItemID: 1
TagID: 12

ItemID: 2
TagID: 11

Now, if I were to do a query with TagIDs 2 and 11, the result would be one row (and one column) only:
ItemID: 1

Item 2 would not be present because it only has tag 11 and not tag 2, if you catch my drift.

[guelphdad]

you want a having clause

SELECT itemid
FROM yourtablename
WHERE tagid IN (2, 11)
GROUP BY
itemid
HAVING COUNT(*) = 2

[Nimlhûg]

It works! Thanks a bunch :)