Firebird how to select identifiers matching all elements of a set

Question

I am using Firebird 2.1.

There is a table: IDs, Labels

There can be several shortcuts for one ID:

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

Say I have a set of labels, for example: (Apple, Pear, Peach).

How can I write one choice to return all identifiers that have all the labels associated in a given set? Preferably, I would like to specify the set in a line separated by commas, for example: ("Apple", "Pear", "Peach") -> this should return ID = 10.

Thanks!

Answer 1

As I said, I am posting my simpler version of piclrow's answer. I tested this on my Firebird, which is version 2.5, but OP (Steve) tested it on 2.1, and it also works.

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

This solution has the same drawback as picrov ... you need to know how many values ​​you are looking for, since the HAVING = condition must match the WHERE IN condition. In this regard, Ed's answer is more flexible as it breaks the string parameter of the combined value and counts the values. Thus, you just need to change one parameter, instead of the two conditions that I and pikkru use.

OTOH, if efficiency is a concern, I would prefer (but I'm absolutely not sure) that the Ed CTE approach may be less optimized using the Firebird mechanism than the one I suggest. Firebird is very good at optimizing queries, but I'm really not right now, if possible, when you use CTE in this way. But WHERE + GROUP BY + HAVING should be optimized by simply specifying the index (id, label).

In conclusion, if the execution time is troubling in your case, you will probably need some explanation to see what happens, whatever decision you make;)

Answer 2

The easiest way is to break the line in the code and then request

 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 

Answer 3

If it is permissible to create an auxiliary stored procedure that will be called from the primary selection, then consider the following.

The Helper stored procedure takes a delimited string along with a 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 the Execute block to demonstrate the passage in the separation line

 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