0

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".

lemon
  • 14,875
  • 6
  • 18
  • 38
Sami
  • 717
  • 6
  • 28
  • 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
  • 44,943
  • 6
  • 31
  • 51
1

You can use the FIND_IN_SET function to check the existence of a category in your categories string, join the two tables and filter out non null values:

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

Check the demo here.

lemon
  • 14,875
  • 6
  • 18
  • 38