CODEWITHSUNDEEP
phpsqlmysql
berty
berty

Reputation: 319

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 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!

Upvotes: 3

Views: 380

Answers (2)

Quassnoi
Quassnoi

Reputation: 425813

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

Upvotes: 4

James Black
James Black

Reputation: 41848

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

Upvotes: 1

Related Questions