SitePoint Sponsor

User Tag List

Results 1 to 2 of 2

Hybrid View

  1. #1
    SitePoint Zealot
    Join Date
    May 2003
    Location
    Sarasota, FL
    Posts
    196
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How is a comma-separated value from a subquery interpreted in an IN() clause?

    I have a table called system_vars that stores among other things a comma-separated list of integers - '66,55,44,33' etc.

    If I use that field in a subquery inside of an IN() statement, is there a way to have the result interpreted as individual integers instead of a single string?

    Example:

    SELECT * FROM `products` WHERE category_id NOT IN(SELECT sys_val FROM system_vars WHERE sys_var = 'excluded_cats');

    * Please do not say "Normalize the table". I know it's not the best design, but it's what I was handed and changing it is not possible at the moment.

    EDIT: But I would be OK if the solution involved creating a normalized temp table. I just wasn't sure how to split the value using only MySQL commands.

    PS: The final solution needs to be usable in a view.
    Last edited by xangelusx; Jan 12, 2011 at 18:29. Reason: clarification
    Chris Bloom
    Web Application Developer

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    you can split the data in two ways, one of them very clumsy using CONCAT with commas and LIKE with commas, the other relatively clean using the FIND_IN_SET function

    but both of those methods do not scale, as they always require a table scan

    normalizing the data would result in searches that are optimized because they can use an index

    table scans are grossly inefficient

    anyhow, you asked me not to tell you to normalize, so i won't

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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