1

given the following table (describing a many-to-many relation):

ID  | PageID | TagID
--------------------
1   | 1      | 1
2   | 1      | 2
3   | 2      | 2
4   | 2      | 3

how do i select 'all PageIDs having all of a list of TagIDs'?
in other words: i want all pages tagged with TagIDs 1 and 2 (so it's only PageID 1 in this example)

after some googling i found the term 'relational division' which might be relevant here, but i didn't quite get it. anyone having a simple query for my problem?

schellmax
  • 5,678
  • 4
  • 37
  • 48

1 Answers1

1

If you have the list of tagids in a table, then it is a simple join and group by:

select pageId
from t join
     list l
     on t.tagId = l.tagId cross join
     (select count(*) cnt from list l) as const
group by pageId
having count(*) = max(cnt)

The having clause checks that all tags are present. If there might be duplicates, then you would want to replace the "count(*)" with "count(distinct tagid)" in both cases.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • correct; just wanted to mention i'm using a slightly simpler query now as described here: http://stackoverflow.com/a/3001855/176140 – schellmax Aug 20 '12 at 10:23