0

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

Sami
  • 665
  • 5
  • 25
  • I know title is not too clear, but I didn't found a better one ... sorry :( – Sami Apr 06 '22 at 13:26
  • please read https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad and for the where clause you can use FIND_IN_SET – nbk Apr 06 '22 at 13:26
  • I would strongly suggest you normalize the database design to 1NF at least. – The Impaler Apr 06 '22 at 13:33

2 Answers2

1

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

D-Shih
  • 42,799
  • 6
  • 22
  • 44
1

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
lemon
  • 2,990
  • 8
  • 28