MySQL "NOT IN" operator only uses the first number in the list?

Question

Running a custom wordpress table to store notices to display to each individual site, I then store the dismissed notices as an option in the site's options table as a list (ie. 3,9,28,22)

Then, when loading the site I need to retrieve the site's notices excluding the ones already dismissed.

SELECT 
    *
FROM
    be_notices as notice
WHERE
    notice.id NOT IN ( SELECT option_value FROM wp_26_options WHERE option_name = 'be_dismissed_notices' )

The problem being it retrieves all the notices as expected, but excludes only the first number/id in the comma-separated list. If I put NOT IN (3,8) hardcoded it works. But that's exactly the value found in option_value so I am confused.

Any idea why it only excludes the first ID and not the second/other one(s)?

visit link given below Hope this will help http://stackoverflow.com/questions/25632137/php-mysql-not-in-array-only-affecting-first-result-in-array — , Sep 03 '16 at 11:50
I could @Strawberry but wouldn't it too much trouble for such a simple feature? — Lazhar, Sep 03 '16 at 12:35
Too much trouble? I think your present solution presents more 'trouble'! — Strawberry, Sep 03 '16 at 12:43
@Strawberry true, just did it with a simple table dismissal_id, site_id, notice_id, dismissed_on.. lets build things the right way :) — Lazhar, Sep 03 '16 at 13:07

score 1 · Accepted Answer · answered Sep 03 '16 at 11:48

1

I used FIND_IN_SET = 0 instead.

WHERE ( FIND_IN_SET( notice.id, ( SELECT option_value FROM $table_options WHERE option_name = 'be_dismissed_notices' ) ) = 0 )

answered Sep 03 '16 at 11:48

Lazhar

1,401
16
37

score 1 · Answer 2 · answered Sep 03 '16 at 13:50

Solution 1 - antijoin (excluding LEFT JOIN):

SELECT notice.*
FROM be_notices as notice
LEFT JOIN wp_26_options as opt
    ON  opt.option_name = 'be_dismissed_notices'
    AND FIND_IN_SET(notice.id, opt.option_value)
WHERE opt.id IS NULL

Solution 2 - NOT EXISTS:

SELECT notice.*
FROM be_notices as notice
WHERE NOT EXISTS (
    SELECT 1
    FROM wp_26_options as opt
    WHERE opt.option_name = 'be_dismissed_notices'
      AND FIND_IN_SET(notice.id, opt.option_value)
)

NOTE that a search with FIND_IN_SET() can not use an index and you will allways end up with a full table/index scan. Normalizing your tables might be a better solution.

MySQL "NOT IN" operator only uses the first number in the list?

2 Answers2