mysql select where in (comma separated)-CodePudding

Table01 with columns

| Id1 | CommaSeparated |
| 1   | 1,2,3          |
| 2   | 4              |
| 3   | 7,8            |

Table02 with columns

| Id2 | SomeValue   |
| 1   | Value01     |
| 7   | Value02     |
| 8   | Value03     |

This works: SELECT SomeValue FROM Table02 WHERE Id2 IN(7,8);. Get Value02 and Value03.

But SELECT SomeValue FROM Table02 WHERE Id2 IN( SELECT CommaSeparated FROM Table01 WHERE Id1 = ? ); does not work (get only Value02). Because it takes only the first character/integer from 7,8.

Tried this

SELECT SomeValue FROM Table02 WHERE FIND_IN_SET ( Id2, ( SELECT CommaSeparated FROM Table01 WHERE Id1 = 3 ) ) > 0;

But returns no results...

Any ideas how to solve? Or better to create another table where "connect" the both tables ("normalize data")?

CodePudding user response：

You can do it using with and json_table to transfrom comma separated string to rows :

   with t1 as (
   select Id1, CommaSeparated
       from table01
    )
    select t2.SomeValue
    from t1
    join json_table(
      replace(json_array(t1.CommaSeparated), ',', '","'),
      '$[*]' columns (CommaSeparated varchar(50) path '$')
    ) t
    join table02 t2 on t2.Id2 = t.CommaSeparated
    where t1.Id1 = 3

Demo here

CodePudding user response：

Solution, using "linking" table.

LinkingTable

| Id | Id1 | Id2 |
| 1  | 3   | 7   |
| 2  | 3   | 8   |

Table01

| Id1 | CommaSeparated |
| 1   | 1,2,3          |
| 2   | 4              |
| 3   | 7,8            |

Table02

| Id2 | SomeValue   |
| 1   | Value01     |
| 7   | Value02     |
| 8   | Value03     |

And sql like this SELECT SomeValue FROM Table02 WHERE Id2 IN( SELECT Id2 FROM LinkingTable WHERE Id1 = 3 );

Only need to change code, while inserting into Table02, need to insert also into LinkingTable and use/take Id1 from Table01

Or in my case, i anyway need to select data from Table01. So another solution SELECT CommaSeparated FROM Table01 WHERE Id1 = 3; For example, result name as $arr_result.

Then

SELECT SomeValue FROM Table02 WHERE Id2 IN( 
rtrim( str_repeat( '?,', count(explode( ',', trim($arr_result[0]['CommaSeparated ']) )) ), ',' ). 
')';

If i use LinkingTable, i also get "waste of resources". Because in LinkingTable need to insert modified lastInsertId from Table02 and Id from Table01.