MySQL Select Statement, WHERE 'IN' Clause

Question

Hello,

I currently have the following row in my table:

         course_data:
             user_id        days     <-- This is a varchar column.
               405          1,3,5

and I am trying to implement the following SELECT statement:

SELECT usrID, usrFirst, usrLast, usrEmail
    FROM tblUsers
    WHERE usrID NOT IN
    (
        SELECT users.usrID
            FROM
                `course_data` courses,
                `tblUsers` users
            WHERE
                days IN ('$day')
    )
    GROUP BY usrID
    ORDER BY usrID

Basically, I want that row (with user 405) to be omitted if the $day variable includes a '1, 3, or 5'.

For example, if $day = "1", it should return an empty query (because the number "1" is in the column "1,3,5").

However, I have not found this to be the case. Even though $day = "1", it still returns that row.

The only way that it won't return the row is if $day= "1,3,5." Yet, I thought that the IN() clause would take any part of my variable and apply it to that column.

Any insights on what I'm doing wrong here? Thanks.

Answer 1

Remove the Quotes in the IN Statement. The Syntax is:

... WHERE column IN (1,2,3)

and not as you used it

... WHERE column IN ('1,2,3')

Also see the documentation on IN, there are more examples.

Answer 2

days does not hold a list of strings. It holds a single string. Is the string "1,3,5" inside one of the strings in {'1'}? Clearly the answer is no. Either refactor days into a different table, or be prepared to do more string manipulation

Answer 3

You should use the like keyword to do a partial match of the char field:

where days like '%1%'

Edit: VolkerK and Lukas Lalinsky both suggest MySQL's find_in_set, which is probably better than like for what you want to do. However, the following recommendation about normalizing your database still applies.

However, you should not store multiple values in a single database field. Instead, consider using two tables:

course_data:
    user_id

course_days:
    user_id
    day_number

Then, you would have the following data:

course_data:
    user_id
    405

course_days
    user_id    day_number
    405        1
    405        3
    405        5

You can then correctly query this schema. For example:

select  cd.user_id
from    course_data as cd
where   cd.user_id not in
    (
        select  course_days.user_id
        from    course_days
        where   course_days.user_id = cd.user_id
            and course_days.day_number = 1
    )

(or, that should be close; I'm not exactly sure what you're trying to accomplish or what the rest of your schema looks like, so this is a best guess).

Answer 4

If I understand your question correctly you want the contents of the varchar field to be treated as a comma-separated list and test whether this list does not contain a certain value. For that you need the find_in_set(str, strlist) function.
But keep in mind that MySQL can't use an index in that case and your query will always need a full table scan. It might be better not to store structured data (and run comparisons on the single elements) in a single column but to use another table and a JOIN as has been suggested in other responses.

Answer 5

I think the query you want is:

SELECT usrID, usrFirst, usrLast, usrEmail
FROM tblUsers
WHERE usrID NOT IN (
    SELECT user_id 
    FROM course_data
    WHERE find_in_set(?, days) > 0
)
ORDER BY usrID

But you should seriously consider normalizing the database, so that each day has it's own row.