MySQL Select Statement, WHERE 'IN' Clause


Question

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.

1
7
10/18/2009 9:59:48 PM

Accepted Answer

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).

11
10/18/2009 9:56:56 PM

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.


Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Icon