#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2011
    Posts
    13
    Rep Power
    0

    Where value in column list problem


    Hi guys,
    I'm facing an issue with the following query.

    select * from table where form.value in (column.list)

    Usually the way queries are set up is the other way around
    meaning :

    select * from table where column.value in (list)

    is there something i can do to make the query work or is there something wrong with my logic?
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,178
    Rep Power
    4274
    your logic is fine

    ... WHERE somevalue IN ( list, of, columns )

    this is valid and will work
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2011
    Posts
    13
    Rep Power
    0
    @r937
    thanks for the reply. one thing perhaps wasn't clear is that column.list is not a list of columns.
    it's a list stored in one field in the DB.

    so it's where 2 in (12,3,4,5,2)

    2 is coming from a form.
    12,3,4,5,2 is stored in the field.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,178
    Rep Power
    4274
    Originally Posted by Abnaxus78
    ...column.list is not a list of columns.
    it's a list stored in one field in the DB.
    very poor design

    you neglected to mention which database system you're using

    if it's mysql, you may use the FIND_IN_SET function, but be aware that it requires a table scan and your query will get slower and slower the more rows you have

    if it's some other database system, things progress downhill, you may have to use a patchwork involving SUBSTRING, and, like mysql, it won't scale
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2011
    Posts
    13
    Rep Power
    0
    i'm using MSSQL.

    I'll see what i can do to change the db design.

    thanks
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,178
    Rep Power
    4274
    Originally Posted by Abnaxus78
    I'll see what i can do to change the db design.
    that's the best strategy all around

    meanwhile, i;ve moved the thread to the MSSQL forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo