I would like to select all IDs that contain a variable set of values.
With data as :
| ID | TYPE | VALUE |
|---|---|---|
| 1 | a | 5 |
| 1 | a | 10 |
| 1 | b | 20 |
| 1 | a | 30 |
| 1 | b | 50 |
| 1 | a | 40 |
| 2 | a | 10 |
| 2 | b | 50 |
| 2 | b | 20 |
| 2 | a | 20 |
| 3 | a | 30 |
| 3 | b | 50 |
| 3 | b | 20 |
| 3 | a | 10 |
| 3 | a | 80 |
| 4 | a | 10 |
| 5 | b | 50 |
a and b can contain a variable set of values (here 2 values each but can be 1 for a and 4 for b like a filter) :
Ex : b in (20,50) and a in (10,30)
Result :
| ID | TYPE | VALUE |
|---|---|---|
| 1 | a | 5 |
| 1 | a | 10 |
| 1 | b | 20 |
| 1 | a | 30 |
| 1 | b | 50 |
| 1 | a | 40 |
| 3 | a | 30 |
| 3 | b | 50 |
| 3 | b | 20 |
| 3 | a | 10 |
| 3 | a | 80 |
CodePudding user response：
Here is an aggregation approach for b in (20,50) and a in (10,30)
SELECT ID
FROM yourTable
GROUP BY ID
HAVING COUNT(CASE WHEN a = 10 THEN 1 END) > 0 AND
COUNT(CASE WHEN a = 30 THEN 1 END) > 0 AND
COUNT(CASE WHEN b = 20 THEN 1 END) > 0 AND
COUNT(CASE WHEN b = 50 THEN 1 END) > 0;
CodePudding user response：
I'd filter the table to just values being searched for (which mimics a=x or a=y) then use a having clause to assert all values must be present (mimicking AND logic rather than OR).
WITH
filter_values AS
(
SELECT
id, type
FROM
your_table
WHERE
(type = 'a' AND value IN (10, 30))
OR (type = 'b' AND value IN (20, 50))
GROUP BY
id, type
HAVING
COUNT(DISTINCT value) = CASE type WHEN 'a' THEN 2 WHEN 'b' THEN 2 END
)
SELECT
id
FROM
filter_values
GROUP BY
id
HAVING
COUNT(*) = 2
Note, however, that this type of search pattern can get quite slow on large tables. Without changing your schema, it's unavoidable. (Search the Web for why Entity-Attribute-Value tables are good for dynamic storage but poor for dynamic search.)