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

MySQL: Get IDs (explode) from string and use it in NOT IN clause

In one table I have such column with data

Source data

Is there possible way to make a query that return this data in this format:

_IDS_
71554
99188
69337
70534
73575

as separate ids that then I can use it for example in query

WHERE table.o_id NOT IN (_IDS_)

Tags:

3

Answers


  1. 0

    Do something like this :

    Sélect * from table1 where id not in (sélect cast(strcol as int) from table2);
    Login or Signup to reply.
  2. 0

    This should do what your looking for

    SELECT * FROM demotbl2 where id not in (SELECT GROUP_CONCAT(TRIM(',' FROM WebImages__images)) FROM demotbl1);

    GROUP_CONCAT() = combines rows, TRIM to remove first and last , which would break the searched array

    Hope it helps

    Here’s a fiddle for proof of concept.. 😀 ..
    Mysql DB Fiddle

    Login or Signup to reply.
  3. 0

    1. Do not store values list in CSV format. Normalize it, store one value per row.

    2. You may test a value against CSV list with FIND_IN_SET() function. You need the tested value not present in any CSV list, so use NOT EXISTS:

    SELECT *
FROM data_table
WHERE NOT EXISTS ( SELECT NULL
                   FROM CSV_table
                   WHERE FIND_IN_SET(data_table.o_id, CSV_table.WebImages__images) 
                   );
    1. You may parse CSV to separate values. There is a lot of solutions. For example:
    SELECT CSV_table.id, jsontable.WebImage_id
FROM CSV_table
CROSS JOIN JSON_TABLE( CONCAT('[', TRIM(BOTH ',' FROM CSV_table.WebImages__images), ']')
                       '$[*]' COLUMNS (WebImage_id INT PATH '$')
                       ) jsontable
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search