CODEWITHSUNDEEP
mysql
Deval Shah
Deval Shah

Reputation: 1094

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

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.

Upvotes: 0

Views: 340

Answers (1)

Kickstart
Kickstart

Reputation: 21523

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

Upvotes: 1

Related Questions