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

In one table I have such column with data

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

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

WHERE table.o_id NOT IN (_IDS_)

CodePudding user response：

Do not store values list in CSV format. Normalize it, store one value per row.
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) 
                   );

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

CodePudding user response：

Do something like this :

Sélect * from table1 where id not in (sélect cast(strcol as int) from table2);

CodePudding user response：

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.. :D .. Mysql DB Fiddle