0

We're struggling with a lightweight way to reduce results in a larger population of search results by excluding rows that don't match ALL of the foreign keys in a second table.

For example, we have a mapping table that matches documents to tags where each document can have an unlimited number of tag relationships:

DocID | TagID
12 | 1
12 | 2
34 | 1
53 | 1
53 | 4
66 | 1
66 | 2
67 | 3

We're building a table in our SPROC based on a list of tagIDs passed in by the user, so we have a second table that looks like this:

TagID
1
2

What we'd like to do is return only the rows from the first table that contain a match on every value in the second table, effectively an "and" query. So if the user passes in tag values 1 & 2 we want to return DocID 12 and 66. Right now our joins return essentially an "or" result, so values 1 & 2 would return DocIDs 12, 34, and 66.

We're currently stuck with MS SQL 2008R2.

Siyual
  • 16,415
  • 8
  • 44
  • 58
Butter Duck
  • 19
  • 1

1 Answers1

4

You can do this with group by and having and a twist:

select docid
from firsttable t1 join
     secondtable t2
     on t1.tagid = t2.tagid
group by docid
having count(*) = (select count(*) from secondtable);

You may need count(distinct) if either table could have duplicates.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786