Mysql - Use a LIKE operator in Subqueries

Question

Who can help me solve this issue?

I have table A with a column with id's. A row can be empty or has one or more id's. If there are more id's in a row then they are comma seperated:

5f7c4738dcf5d
5f8571d05cffb,5f8574dc6a681,5f99d6d30b3ad
null
5f8ff7f6514ef,5f8fd1eac16a7

In table B I have also id's. But just one id per row.

Now I want to select all the rows in table B if the same id does not exist in table A.

I tried this query:

SELECT * 
FROM B 
WHERE NOT EXISTS (SELECT ids FROM A WHERE A.ids LIKE '%B.id%')

If i use the = operator (instead of LIKE) then it works but the comma seperated values are not selected.

How can I compare the comma separated values?

Focus on the last line of GMB's answer – Strawberry Nov 02 '20 at 08:34 — Strawberry, Nov 02 '20 at 08:34

score 0 · Accepted Answer · answered Nov 01 '20 at 22:30

0

You can use not exists and find_in_set():

select b.*
from b
where not exists (select 1 from a where find_in_set(a.ids, b.id))

That said, your primary effort should go into fixing your data model. Storing CSV lists in a relational dabatase is bad practice, and should be avoided; instead, each element of a(ids)should go on a separate row. Recommended reading: Is storing a delimited list in a database column really that bad?.

answered Nov 01 '20 at 22:30

GMB

216,147
25
84
135

Thank you, it works. Also thanks the recommendation, I was not aware of this! I will fix this with a link table. – Mano Nov 02 '20 at 11:29

Mysql - Use a LIKE operator in Subqueries

1 Answers1