All geek questions in one place

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

I have a product table with a column that contains a list of identifiers separated by spaces (for example: "23 42 365"), the column is called "categories". Numbers refer to row identifiers in another table (category).

I need to extract all product lines where all identifiers in a space-separated list point to lines in the category table that no longer exist.

I know that this is far from the best database design, but I was given this task in an older system. I'm not even sure that this can be done completely with the SQL statement, but due to the large number of entries in the product table, it would be slower to use PHP logic to determine the returned rows. However, if this is the only way, this is what I will do!

+3
source share
2 answers
SELECT  m.*
FROM    mytable m
LEFT JOIN
        categories c
ON      FIND_IN_SET(c.id, REPLACE(m.categories, ' ', ','))
WHERE   c.id IS NULL
+4
source

You can use an outer join to get the missing lines by looking for where the category is null.

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

+1
source

Source: https://habr.com/ru/post/1728446/

More articles:


All Articles