0

I've 2 tables.

First with Id and Name: like TAB_FIRST.first_id as INT and TAB_FIRST.first_name as VARCHAR.

The second table as unique ID, name and (that's the key...) a VARCHAR field with a list of Id from the first table. So this field contain data like "26,27,29,28,25,35"

I want to get data from the first table, which Ids are not in the list of If of the second one.

If I perform:

SELECT
first_id,
first_name
FROM TAB_FIRST
WHERE first_id NOT IN (26,27,29,28,25,35)

I works. If I use NOT IN ('26','27','29','28','25','35') it works too.

But when I remplace the hard coded list by a select, the NOT IN don't filter the data and I get all data from first table. So:

SELECT
first_id,
first_name
FROM TAB_FIRST
WHERE first_id NOT IN (SELECT second_list_id FROM TAB_SECOND)

doesn't work.

I'm pretty sure this came from the fact I try to perform the NOT IN between an INT (first_id) and a list which is a "string". I tried to CAT/CONVERT first_id, try to perform a LOCATE on the list adding '' and so on. Each time same result. Is there a way to do that (without using a third table to link id from first table with id from second one).

Thanks a lot

Peter
  • 1,247
  • 19
  • 33
  • 1
    You should fix your data model and stop storing CSV in the `second_list_id` column. – Tim Biegeleisen Apr 11 '20 at 12:56
  • @Tim Biegeleisen I perfectly know it would be the best way. The problem is that the design has been made this way. So is there an other option, so performing a NOT IN between an INT and a list? That's the question. – Peter Apr 11 '20 at 13:07
  • As reply made by Shadow, this https://stackoverflow.com/questions/4171832/comma-separated-argument-for-in-operator-mysql give the answer, using find in set. – Peter Apr 11 '20 at 14:01

1 Answers1

1

The problem is that second_list_id is not the SQL array that it would expect within NOT IN, it's just a string with comma-separated IDs in it, as you said. You would need to parse/split this into a proper array for the NOT IN matching to work as expected. How you do this depends on what dialect of SQL you're using:

Another option would be to just perform 2 queries; the first to pull out second_list_id, then parse that in code, and another to query with your NOT IN using that parsed list of IDs.

snormore
  • 412
  • 2
  • 7