Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    106

    Unanswered: Integer, varchar and IN

    In my database I have a table called "users" with a varchar-field that holds categori-id's commaseparated, collected from my other table "category". I do this to control access for my users, some users are only allowed access to some categories. I would like to run a statement sort of like this:
    Code:
    select categoryname
      from category
    where catid in (select categories from user where userid = 12)
    Naturally, because catid and the field categories have different datatypes I get the error "Syntax error converting the varchar value '340, 344, 356' to a column of data type int."

    Is there any way I can bypass this keeping the commaseparated values and without making a new normalized table instead?

    (same question is also posted at sql-server-performance.com forums)
    Frettmaestro
    "Real programmers don't document, if it was hard to write it should be hard to understand!"

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002

    Re: Integer, varchar and IN

    Originally posted by Frettmaestro
    Is there any way I can bypass this keeping the commaseparated values and without making a new normalized table instead?
    there are several ways, all of them, um, how do i put this, less than elegant
    (same question is also posted at sql-server-performance.com forums)
    i'd be interested in hearing about any more clever solutions than mine:
    Code:
    select C.categoryname
      from user U
    inner
      join category C
        on instr(','+C.catid',', ','+U.categories+',') > 0
     where U.userid = 12
    the concatenation of a comma in front of and behind the fields is so that you will find the first or last category in the list

    and of course the search string is delimited by commas, so that you don't accidentally find 12 inside 312
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    106
    Thanx alot for the contribution...are you a vb/asp-developer? This syntax is alot like asp/vb but unfortunately there is no such thing as "instr" in sql. I have used InStr alot in my asp-scripting but "IN" and "LIKE" are the only keywords I can think of that does close to the same thing allthough I cannot make them work. It's not a big deal to make the extra table actually...it was really more out of curiosity I was asking...
    Frettmaestro
    "Real programmers don't document, if it was hard to write it should be hard to understand!"

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    vb/asp? me? heh, no way, not even close

    my mistake, change INSTR to CHARINDEX
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    but yeah, create a normalized table

    for one thing, your queries won't do table scans and can start using indexes

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    106
    This is the final version and it worked like a charm allthough I think I will go with the normalized table as you are recomending.
    Code:
    select C.category
      from user U
    inner
      join category C
        on CHARINDEX(',' + CAST(C.catid AS varchar(20)) + ',', U.categories) > 0
     where U.userid = 12
    It really doesn't make that big difference because this will probably only be used by 3-4 users maybe a couple of times a week, but still I prefer to create quality stuff instead of shit when I can. But thanx alot buddy for all your help, I might be back to bother you again real soon
    Frettmaestro
    "Real programmers don't document, if it was hard to write it should be hard to understand!"

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you have to put commas around U.categories too, otherwise you won't find the first or last item

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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