find rows with column C not in list of values of another table-CodePudding

I have two tables A and B

Table A
id | name | category_id
-----------------------
1  | foo  | cat1
2  | bar  | cat2
3  | foob | cat1
4  | booh | cat999

Table B
id | categories
-----------------------
1  | cat1, cat3
2  | cat2, cat1, cat3
3  | cat1

I'd like to find rows of table A with A.category_id NOT in B.categories. As you see the problem is B.categories are comma separated values, I don't see how to use LIKE in such case !

In my example I must be able to find row #4 of table A

CodePudding user response：

You can try to use NOT EXISTS with FIND_IN_SET

SELECT a.*
FROM A a 
WHERE NOT EXISTS (
  SELECT 1
  FROM B b
  WHERE FIND_IN_SET(a.category_id, b.categories)
)

sqlfiddle

CodePudding user response：

You can select NULL values using a LEFT JOIN:

SELECT
    A.category_id
FROM 
    A
LEFT JOIN
    B
ON
    FIND_IN_SET(A.category_id, B.categories)
WHERE
    B.categories IS NULL