SQL Query to get rows where values in a column do not exist in another table

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 开发者_Go百科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!

---

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

---

You can use an outer join to get the missing rows, searching for where the category is null.

http://dev.mysql.com/doc/refman/5.0/en/join.html