MySQL Select, where field is not in a CSV list in another field

Question

I have a table like this

fieldA  | fieldB
-----------------
a       | a,b,c
-----------------
b       | a,c
-----------------
c       | x,y,z

where fieldA is a string and fieldB is a CSV list.

I need to build a query that would do something similar to what I was hoping to get from this query:

SELECT * FROM table WHERE fieldA NOT IN fieldB

(would select if fieldA is not on the CSV list in fieldB)

or an inferior but usable query might me something like this:

SELECT * FROM table WHERE fieldA NOT LIKE %fieldB%

(would select if fieldA is not a substring of fieldB)

Result would be something like this:

fieldA  | fieldB
-----------------
b       | a,c
-----------------
c       | x,y,z

@hbgoddard "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'fieldB" and "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '%fieldB%", respectively. I know this should work if I had a specific array or a string (respectively) but for some reason this doesn't work when comparing a field to another field — Andri, Aug 08 '18 at 22:53

Shubham · Accepted Answer · 2018-08-10T12:01:38.917

2

SELECT * FROM table WHERE  NOT FIND_IN_SET(fieldA,fieldB)

Your are looking for FIND_IN_SET in mysql.

NOTE: FIND_IN_SET is known to perform poorly. Do think twice before using in production environment

edited Aug 10 '18 at 12:01

answered Aug 08 '18 at 23:34

Shubham

Great, works perfectly :) I ended up doing this in PHP instead when I wasn't finding the answer. Given your warning about performance, I'll stick to the PHP. But this is definitely what I was asking for. – Andri Aug 09 '18 at 15:38

1 Answers1