How to use find_in_set for two strings to match in target or anything?

Question

I have following below 2 tables.Here is schema Sqlfiddle for it.

Table 1    
    Transaction  Items        
-----------  -------------
T1           I1,I3,I7     
T2           I7,I2,I3     
T3           I1,I2,I3     
T4           I2,I3        
T5           I2,I3,I4,I5  

Table 2 
    Id  Items   
------  --------
     1  I1,I3   
     2  I1,I2   
     3  I2,I4   
     4  I2,I3   
     5  I4,I5

I want result in Table 3 like for each record in Table2 like 1st row I1,I3how many time it occurs in Table 1 in each record.It should display in SOT column as answer.Here for 1st one is 2.

Table 3
    Id  Items      SOT  
------  ------  --------
     1  I1,I3          2
     2  I1,I2          1
     3  I2,I4          1
     4  I2,I3          4
     5  I4,I5          1

Can you please advise me for this? I have think of find_in_set but It works for only 1 string to match.

[Is storing a delimited list in a database column really that bad?](http://stackoverflow.com/a/3653574) — eggyal, Mar 06 '14 at 09:33
While it is possible to split up the string and then search through the other matches using FIND_IN_SET, it would be horrible to write, hideous to read, slow to run and a nightmare to maintain. — Kickstart, Mar 06 '14 at 09:50
Actually, just to be irritating, FIND_IN_SET is blisteringly fast! :-( — Strawberry, Mar 06 '14 at 09:59
@Strawberry: Define "*blisteringly fast*"? It's not sargable so will require a full table scan, with string comparisons on every record. Relative to joining (a large table) using indexes, the only "blistering" that will be involved will be what happens to one's fingers as one taps impatiently on the table awaiting the results. — eggyal, Mar 06 '14 at 10:07
FIND_IN_SET might be fairly fast for what it is doing, but as you have to split up the items field on table 2, that will slow things down. — Kickstart, Mar 06 '14 at 10:34

score 1 · Accepted Answer · answered Mar 06 '14 at 10:46

As a demonstration, the following SQL will get you the results you want (I think) with up to 100 comma separated values in Table2.Items.

As you can see it is not pleasant to read, and anyone who comes to maintain this statement in the future would probably be very confused. I would not recommend doing something like this in live code.

SELECT Id, COUNT(*)
FROM
(
    SELECT Transaction, anItemCount, ItemVal.Id, COUNT(anItem) AS aCount
    FROM
    (
        SELECT DISTINCT Id, SUBSTRING_INDEX(SUBSTRING_INDEX(Items, ',', AnInt), ',', -1) AS anItem
        FROM Table2,
        (
            SELECT 1 + Units.i + Tens.i * 10 as AnInt
            FROM
            (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) Units,
            (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) Tens
        ) Ints
    ) ItemVal
    INNER JOIN
    (
        SELECT Id, COUNT(DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(Items, ',', AnInt), ',', -1)) AS anItemCount
        FROM Table2,
        (
            SELECT 1 + Units.i + Tens.i * 10 as AnInt
            FROM
            (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) Units,
            (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) Tens
        ) Ints
        GROUP BY Id
    ) ItemCnt
    ON ItemVal.Id = ItemCnt.Id
    INNER JOIN Table1
    ON FIND_IN_SET(ItemVal.anItem, Table1.Items)
    GROUP BY Transaction, anItemCount, ItemVal.Id
    HAVING anItemCount = aCount
) Sub1
GROUP BY Id

If Table2.Items only ever contains 2 values then this could be cut down to:-

SELECT Id, COUNT(*)
FROM
(
    SELECT Table1.Transaction, ItemVal.Id, COUNT(anItem) AS aCount
    FROM
    (
        SELECT Id, SUBSTRING_INDEX(Items, ',', 1) AS anItem
        FROM Table2
        UNION
        SELECT Id, SUBSTRING_INDEX(Items, ',', -1) AS anItem
        FROM Table2
    ) ItemVal
    INNER JOIN Table1
    ON FIND_IN_SET(ItemVal.anItem, Table1.Items)
    GROUP BY Table1.Transaction, ItemVal.Id
    HAVING aCount = 2
) Sub1
GROUP BY Id;

It could also be done simply when there are only 2 values in Table2.Items with the following:-

SELECT Table2.Id, COUNT(Table1.Transaction) AS aCount
FROM Table2 
INNER JOIN Table1
ON FIND_IN_SET(SUBSTRING_INDEX(Table2.Items, ',', 1), Table1.Items)
AND FIND_IN_SET(SUBSTRING_INDEX(Table2.Items, ',', -1), Table1.Items)
GROUP BY Table2.Id

But still hardly pleasant.

SQL Fiddle here:-

http://www.sqlfiddle.com/#!2/03fe9/19

How to use find_in_set for two strings to match in target or anything?

1 Answers1

Linked