Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2004
    Posts
    2

    Arrow Unanswered: IN Clause not seeing val in comma-delim list?

    Hello,

    The first query below is not returning my row? It should... I am at a loss. The stored value in a.genrePIDs is "131,79". If the value is 131, 2nd query below, then my row is returned. But no row is returned if I am looking for 79. If I add the string '79' between the (,'79')'s, 3rd query below, then my row is returned.

    Any ideas?


    SELECT a.trackID
    FROM tracks a
    WHERE 79 IN (a.genrePIDs) <- ROW IS NOT RETURNED


    SELECT a.trackID
    FROM tracks a
    WHERE 131 IN (a.genrePIDs) <- ROW IS RETURNED


    SELECT a.trackID
    FROM tracks a
    WHERE 79 IN (a.genrePIDs,'79') <- ROW IS RETURNED


    Using: MySQL 4.1

    Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    this is a great example of why they tell you never to store a comma-separated list of values in a single column

    it breaks first normal form and makes joining on the column next to impossible

    can you redesign the table properly?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2005
    Posts
    10
    jap looks like a refactoring is on schedule

    you can do someting like this

    make a assignment table
    track_id, genre_id both smalest type you expect
    mediumint 8 unsigned should do it (if no negative values are needed)

    and later do a subselect
    somthing like
    WHERE X IN (SELECT genre_id FROM assignment_tabe b WHERE b.track_id = a.track_id )

    i like the 4.1 version :0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •