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!

Jointress

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 ;)

Amargo

There is no CTE ("common table expression") in your (or pilcrow's) query – Celtic

That comment was referred to Ed's answer, which is nice and flexible, but does use CTE. I'll make it clearer. Thanks – Amargo

Works with FB2.1 too. I'll take this as the answer as this is the simplest query. Thanks! – Jointress

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 

Numberless

What if (id, label) is not unique? I'd add a DISTINCT in the subselect... just in case ;) – Amargo

I haven't used Firebird in a while, and I did not use it to do this type of query. Can't this be done without the SUBSELECT in FireBird? I mean... using a HAVING instead of the WHERE in the outer select? – Amargo

@Amargo could you post your simpler version? – Jointress

Answer 3

If it is acceptable to create a helper stored procedure that will be called from the primary select then consider the following.

The Helper stored procedure takes in a delimited string along with the delimiter and returns a row for each delimited string

CREATE OR ALTER PROCEDURE SPLIT_BY_DELIMTER (
    WHOLESTRING VARCHAR(10000),
    SEPARATOR VARCHAR(10))
RETURNS (
    ROWID INTEGER,
    DATA VARCHAR(10000))
AS
DECLARE VARIABLE I INTEGER;
BEGIN
    I = 1;   
    WHILE (POSITION(:SEPARATOR IN WHOLESTRING) > 0) DO
    BEGIN
        ROWID = I;
        DATA = TRIM(SUBSTRING(WHOLESTRING FROM 1 FOR POSITION(TRIM(SEPARATOR) IN WHOLESTRING) - 1));        
        SUSPEND;      
        I = I + 1;
        WHOLESTRING = TRIM(SUBSTRING(WHOLESTRING FROM POSITION(TRIM(SEPARATOR) IN WHOLESTRING) + 1));
    END
    IF (CHAR_LENGTH(WHOLESTRING) > 0) THEN
    BEGIN
        ROWID = I;
        DATA = WHOLESTRING;
        SUSPEND;
    END
END

Below is the code to call, I am using Execute block to demonstrate passing in the delimited string

EXECUTE BLOCK
RETURNS (
    LABEL_ID INTEGER)
AS
DECLARE VARIABLE PARAMETERS VARCHAR(50);
BEGIN
  PARAMETERS = 'Apple,Peach,Pear';

  FOR WITH CTE
  AS (SELECT ROWID,
             DATA
      FROM SPLIT_BY_DELIMITER(:PARAMETERS, ','))
  SELECT ID
  FROM TABLE1
  WHERE LABELS IN (SELECT DATA
                   FROM CTE)
  GROUP BY ID
  HAVING COUNT(*) = (SELECT COUNT(*)
                     FROM CTE)
  INTO :LABEL_ID
  DO
    SUSPEND;
END

Fong