Firebird how to select ids that match all items in a set

Question

I'm using Firebird 2.1.

There is a table: IDs, Labels

There can be multiple labels for the same ID:

10 Peach
10 Pear
10 Apple
11 Apple
12 Pear
13 Peach
13 Apple

Let's say I have a set of labels, ie.: (Apple, Pear, Peach).

How can I write a single select to return all IDs that have all labels associated in a given set? Preferably I'd like to specify the set in a string separated with commas, like: ('Apple', 'Pear', 'Peach') -› this should return ID = 10.

Thanks!

Answer 1

As asked, I'm posting my simpler version of piclrow's answer. I have tested this on my Firebird, which is version 2.5, but the OP (Steve) has tested it on 2.1 and it works as well.

SELECT id
FROM table
WHERE label IN ('Apple', 'Pear', 'Peach')
GROUP BY id
HAVING COUNT(DISTINCT label)=3

This solution has the same disadvantage as pilcrow's... you need to know how many values you are looking for, as the HAVING = condition must match the WHERE IN condition. In this respect, Ed's answer is more flexible, as it splits the concatenated value string parameter and counts the values. So you just have to change the one parameter, instead of the 2 conditions I and pilcrow use.

OTOH, if efficency is of concern, I would rather think (but I am absolutely not sure) that Ed's CTE approach might be less optimizable by the Firebird engine than the one I suggest. Firebird is very good at optimizing queries, but I don't really now if it is able to do so when you use CTE this way. But the WHERE + GROUP BY + HAVING should be optimizable by simply having an index on (id,label).

In conclusion, if execution times are of concern in your case, then you probably need some explain plans to see what is happening, whichever solution you choose ;)

Answer 2

It's easiest to split the string in code and then query

SQL> select ID
CON>   from (select ID, count(DISTINCT LABEL) as N_LABELS
CON>           from T
CON>          where LABEL in ('Apple', 'Pear', 'Peach')
CON>          group by 1) D
CON>  where D.N_LABELS >= 3;  -- We know a priori we have 3 LABELs

          ID 
 ============ 
           10