Select the rows having a specific set of characteristics

Question

Apologies for the hand-wavy title, I honestly couldn't come up with a better description while keeping the text short. Here's the full problem.

In a table with two columns, I'd like to select the one entry in the first column that has all the values I specify in the second column. This is better explained with a toy example (also on SQL Fiddle).

Suppose I have a table like this:

CREATE TABLE t (fruit TEXT, color TEXT);

INSERT INTO t (fruit, color) VALUES
('banana', 'green'),
('banana', 'yellow'),
('apple', 'green'),
('apple', 'red'),
('apple', 'yellow'),
('strawberry', 'green'),
('strawberry', 'red');

Given a number of colors, I'd need to retrieve the fruit (you can assume there will be only one) that has all those colors - not more, not less.

So if I query for "green and red" I should get "strawberry". However, if I query for "green and yellow" I should see "banana" and not "apple" because the latter also has an additional color.

So far what I've come up with is a horrible hack using INTERSECT:

WITH cte AS (
  SELECT fruit FROM t WHERE color = 'green'
  INTERSECT SELECT fruit FROM t WHERE color = 'yellow')
SELECT fruit FROM cte;

which of course fails, because returns both banana and apple.

Anyone has a better plan?

score 2 · Answer 1 · 2021-01-15T12:21:08.583

You can use a boolean aggregate for this:

select fruit
from the_table
group by fruit
having bool_and(color in ('green', 'yellow'));

If you want to do it with INTERSECT you could use something like this:

-- all fruits that have at least those two colors 
select fruit
from t
where color in ('green', 'yellow')
group by fruit
having count (distinct color) = 2
intersect 
-- all fruits that have exactly two colors 
select fruit
from t
group by fruit
having count(distinct color) = 2

S-Man · Accepted Answer · 2021-01-15T12:05:06.490

1

demo:db<>fiddle

You can aggregate the colors and check if the aggregated array equals the array with your expected colors.

SELECT
    fruit
FROM t
GROUP BY fruit
HAVING ARRAY_AGG(color ORDER BY color) = '{green, yellow}'

demos:db<>fiddle

To get an order independent solution you could for example:

SELECT
    fruit
FROM t
GROUP BY fruit
HAVING ARRAY_AGG(color) @> '{yellow, green}'
   AND ARRAY_AGG(color) <@ '{yellow, green}'

or

SELECT
    fruit
FROM t
GROUP BY fruit
HAVING ARRAY_AGG(color ORDER BY color) = 
   (SELECT ARRAY_AGG(unnest ORDER BY unnest) FROM unnest('{yellow, green}'::text[]))

edited Jan 15 '21 at 12:05

answered Jan 15 '21 at 11:51

S-Man

22,521
7
40
63

I ended up choosing this answer and extended it a little bit so as to be able to filter on more columns (the toy example I made was a bit too simplistic). And since the order was important I dropped the `ORDER BY` clause. In the end it looked something like `SELECT fruit FROM t GROUP BY fruit HAVING ARRAY_AGG(color) = '{green, yellow}' AND ARRAY_AGG(size) = '{S, M}';`. – Jir Jan 15 '21 at 14:41

Select the rows having a specific set of characteristics

2 Answers2