SQL Query用于获取另一个表中不存在列中值的行

Question

I have a products table with a column that contains a space separated list of ids (like: "23 42 365"), the column is called "categories". The numbers refer to rows ids in another table (categories).

I need to extract all product rows where all of the ids in the space separated list point to rows on the categories table that no longer exist.

I know this is not the best database design by a long way, however I have been presented with this task on an older system. I am not even sure it can be done entirely with an SQL statement, but because of the sheer number of records on the product table, it would be slower to use PHP logic to determine the rows to return. However, if thats the only way, thats what i'll do!

Answer 1

SELECT  m.*
FROM    mytable m
LEFT JOIN
        categories c
ON      FIND_IN_SET(c.id, REPLACE(m.categories, ' ', ','))
WHERE   c.id IS NULL