Question 20960 in Mysql Question posted in
The official MySQL documentation can be found here.

mysql select where in (comma separated)

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")?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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.

    The third solution (a bit "crazy").

    $sql_select = '
SELECT ( SELECT IFNULL ( 
( SELECT `T02`.`SomeValue` 
FROM Table01 `T01` 
INNER JOIN Table02 `T02` 
ON FIND_IN_SET( `T02`.`Id2`, `T01`.`CommaSeparated` ) 
WHERE `T01`.`Id1 ` = 3 AND <another condition>
)
, "empty first value" ) ) 

UNION 

SELECT ( SELECT IFNULL ( 
( SELECT `T02`.`SomeValue` 
FROM Table01 `T01` 
INNER JOIN Table02 `T02` 
ON FIND_IN_SET( `T02`.`Id2`, `T01`.`CommaSeparated` ) 
WHERE `T01`.`Id1 ` = 3 AND <another condition>
)
, "empty second value" ) ) 
;';

    And $arr_select = $stmt_select->fetchAll(PDO::FETCH_COLUMN, 0);

    Get one dimensional array. May use if know maximum number of values (how many values, so many union).

    At the moment conclusion is that LinkingTable solution would be better (less possibility to get something unexpected and, yes, seems in total uses less system resources) ...


  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search