Finding items in a table that satisfy a subset frequency condition?

Question

I have a very simple item ownership table with these two columns:

UserID, ItemID

There is an index on UserID, but not ItemID.

I have a set S of 10-40 specific ItemIDs (in my queries they are just a comma delimited list of integers).

I want to find all UserIDs that own at least X (distinct ItemIDs) of the items in S.

I am using MSSQL if it matters. Can this be done efficiently?

score 3 · Accepted Answer · answered Dec 21 '11 at 18:01

3

select UserID
from Ownership 
where ItemID in (1,2,3,4,5,...) --your list of ItemIDs
group by UserID
having count(distinct ItemID) >= 3 --the minimum # of distinct items required

answered Dec 21 '11 at 18:01

D'Arcy Rittich

167,292
40
290
283

That is the elegant solution I was looking for. Thanks! – John Shedletsky Dec 21 '11 at 18:14
@RedFilter: Agree. That's why it was [initially called SEQUEL](http://en.wikipedia.org/wiki/SQL#History) – Structured English QUEry Language. – Andriy M Dec 21 '11 at 21:45
Sounds like an internationalization headache! – John Shedletsky Dec 22 '11 at 00:06

Finding items in a table that satisfy a subset frequency condition?

1 Answers1