Select rows that meet ALL of a dynamic set of ids

Question

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

Does it matter if they have *other* tokens also? Say there was also `EE, 1234` — Charlieface, May 09 '22 at 22:03
Token Address table gets populated each hour via a script. Token Sets can be created at any time and are based by selecting TokenIDs and creating a Set. I want to be able to run a query to get a List of Addresses that have TokenSets So in this case Address "1234" have a TokenSet "SetA" — userStack, May 10 '22 at 20:21
So what result do you want if there are `TokenAddress` rows which are *not* part of `SetA` do we just ignore those and ensure that all `SetA` rows *are* present? Or do you want to have an exact match for the whole set of rows — Charlieface, May 10 '22 at 20:45
Your answer below seems to be working for me, but I need to add more data to make 100% sure but just having the concept helps a ton to work off. I'm basically trying to look at what TokenSets an Address has and then also what Addresses a TokenSet has. I have been able to to take what you provided to get a working version for both of my cases. — userStack, May 11 '22 at 21:18

The Impaler · Answer 1 · 2022-05-10T21:36:14.143

2

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 set = 'SetA')

edited May 10 '22 at 21:36

answered May 09 '22 at 18:56

The Impaler

45,731
9
39
76

I get the following error: Column 'token_sets.set' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause. – userStack May 10 '22 at 20:37

score 2 · Accepted Answer · answered May 09 '22 at 22:19

2

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

answered May 09 '22 at 22:19

Charlieface

52,284
6
19
43

At first test this seems to be working as expected, thanks also for the link to do more research into the RD – userStack May 10 '22 at 20:40

Select rows that meet ALL of a dynamic set of ids

2 Answers2