Select rows that meet ALL of a dynamic set of ids-CodePudding

I have a table that I want to be able to query to get rows that are in a defined sets of value. I can create new table structures if required as this is temporary data used for a leaderboard of sorts.

The Token Sets can be created by grouping many tokens together. What I want to do it find all the Addresses that have ALL tokens in a defined set.

Not sure if I need to re model the data, or if I'm just missing a simple query setup.

So in the data below I would want to return Address 1234 based on TokenSet "SetA".

Token Address

TokenID	Address
AA	1234
BB	1234
CC	1234
DD	1234
AA	4321
CC	4321

Token Sets

TokenID	Set
AA	SetA
BB	SetA
CC	SetA

CodePudding user response：

If sets and addresses contains non-repeated tokenIDs, you can do:

select a.address
from token_sets s
join token_address a on a.tokenid = s.tokenid
where s.set = 'SetA'
group by a.address
having count(*) = (select count(*) from token_sets where s.set = 'SetA')

CodePudding user response：

This is classic Relational Division. There are quite a number of different solutions.

If you want to do this across multiple sets at the same time, one way is to use a window function

SELECT
  ts.[Set],
  ta.Address
FROM TokenAddress ta
JOIN (
    SELECT *,
      Total = COUNT(*) OVER (PARTITION BY ts.[Set])
    FROM TokenSet ts
) ts ON ta.TokenID = ts.TokenID
GROUP BY
  ts.[Set], ta.Address
HAVING COUNT(*) = MIN(ts.Total);

SQL Fiddle