SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Member
    Join Date
    Dec 2004
    Location
    Las Vegas, NV
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    comma delimited string in nvarchar, matching with user id

    Okay heres the deal (I hope I can explain this clearly enough while being as breif as I can, and I apologize if this has been covered in another thread but I saw nothing similar):

    I created a trouble ticket system (ASP site using MS SQL) where the trouble tickets were assigned to only one person, but now I need tickets to be assigned to multiple people.

    So in my ticket table I changed the assigned_to field to nvarchar and the user id's are comma delimited.

    On the users home page, when they sign into the site I show currently open tickets assigned to that user.

    Initially my SQL statement looked like this:

    strSQL = "SELECT t.ticket_id as [Ticket ID], t.priority, t.subject, b.name as [Client] FROM tickets t, users u, business b where u.user_id = t.assigned_to and t.assigned_to = "& session("user_id")&" and t.status = 'In Progress' and b.business_id = t.business_id"
    Now as the field type has changed I went looking for something to help me sort thru the comma delimited field to find tickets assigned to the logged in user and am somewhat close with this:

    strSQL = "SELECT t.ticket_id as [Ticket ID], t.priority, t.subject, b.name as [Client] FROM tickets t, users u, business b where u.user_id = t.assigned_to and (CHARINDEX(',"& session("user_id") &",',t.assigned_to)>0 OR CHARINDEX('"& session("user_id") &",',t.assigned_to)=1 OR CHARINDEX(',"& session("user_id") &"',t.assigned_to)=LEN(t.assigned_to)-1) and t.status = 'In Progress' and b.business_id = t.business_id order by b.name, t.ticket_id"

    or

    strSQL = "SELECT t.ticket_id as [Ticket ID], t.priority, t.subject, b.name as [Client] FROM tickets t, users u, business b where u.user_id = "& session("user_id") &" and (PATINDEX('%,"& session("user_id") &",%',t.assigned_to)>0 OR PATINDEX('%"& session("user_id") &",%',t.assigned_to)=1 OR PATINDEX('%,"& session("user_id") &"%',t.assigned_to)=LEN(t.assigned_to)-1) and t.status = 'In Progress' and b.business_id = t.business_id order by b.name, t.ticket_id"
    neither of which work that well as it will return records that aren't assigned to that user at all and I am nto sure why. I just found charindex() and patindex() and barely know how to use them properly.

    Please help me as I am at my wits end.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i would recommend that you redesign the table yet once more

    move the assigned_to to a separate table, and change it back to integer, to act as foreign key to the user

    it's a many-to-many relationship, and you'll save yourself a world of hurt by making the change now
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Dec 2004
    Location
    Las Vegas, NV
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thats a good idea, but I had already done a lot of work on the trouble ticket creation and view ticket/update ticket pages to set it up the way it is currently.

    Is what I am wanting to do impossible?

    Thanks for taking the time to help me, I really appreciate it.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    not impossible, but slow as cold glue
    Code:
    SELECT t.ticket_id as [Ticket ID]
         , t.priority
         , t.subject
         , b.name as [Client] 
      FROM tickets t
    inner
      join business b 
        on b.business_id = t.business_id
     where ','+t.assigned_to+',' 
      like '%,'+session("user_id")+',%'   
       and t.status = 'In Progress'
    seriously, redesign now, there are likely other queries which will suffer from this kluge
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Dec 2004
    Location
    Las Vegas, NV
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    oh hey I did it! I tried your last one but I couldn't get it to work right, although I really truely appreciate your time and help.

    strSQL = "SELECT t.ticket_id as [Ticket ID], t.priority, t.subject, b.name as [Client] FROM tickets t, business b where (t.assigned_to like '%,"& session("user_id") &",%' OR t.assigned_to like '%"& session("user_id") &",%' OR t.assigned_to like '%,"& session("user_id") &"%' or t.assigned_to like '"& session("user_id") &"') and t.status = 'In Progress' and b.business_id = t.business_id order by b.name, t.ticket_id"
    I am so lame sometimes, I had the users table in there for no reason whatsoever because of data that I used to display in the results but don't anymore.

    I know that you are right and it would probably be alot better if I had a seperate table but I really don't want to redo All the work I just redid

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you don't have to do this --
    Code:
     where (
           t.assigned_to like '%,"& session("user_id") &",%' 
        OR t.assigned_to like '%"& session("user_id") &",%' 
        OR t.assigned_to like '%,"& session("user_id") &"%' 
        or t.assigned_to like '"& session("user_id") &"'
           )
    it's not quite so easy to see it with that honking great parameter in there, so let's simplify it a bit

    let's say that t.assigned_to is '3,5,9,37' and let's say that the user_id session variable is 3

    your code would say --
    Code:
     where (
           t.assigned_to like '%,3,%' 
        OR t.assigned_to like '%3,%' 
        OR t.assigned_to like '%,3%' 
        or t.assigned_to like '3'
           )
    but you really don't need this complexity

    not only is it complex, it's also wrong!

    if t.assigned to were '4,5,9,37' it would incorrectly match '%,3%')

    i know what you were thinking, you have to test for user_id 3 inside the list of numbers in t.assigned_to, and also at the beginning of the list, at the end of the list, or equal to the entire list

    but what if you concatenate a comma to the front and back of the list?

    so instead of using t.assigned_to, which is '3,5,9,37', you used ',3,5,9,37,' instead?

    then what you could do is search for ',3,' instead of searching for 3

    notice that by searching for ',3,' you won't accidentally return true for 37

    now go back to post #4 and see how i did the WHERE clause with just one comparison, not the four that you used

    oh, and by the way, i understand that you have put a lot of work into this design, but that's not enough reason to stick with it -- perhaps what you don't understand is how much unnecessary heartache and extra work you are still facing

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

  7. #7
    SitePoint Member
    Join Date
    Dec 2004
    Location
    Las Vegas, NV
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    omg yer absolutely right!

    I think I will take the "concatenate a comma to the front and back of the list" suggestion.

    a thousand thank yous!

  8. #8
    SitePoint Enthusiast niardica's Avatar
    Join Date
    Sep 2002
    Location
    CA
    Posts
    34
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Obviously he's right.. Its rediculous to store a csv in a field and search it like that.

  9. #9
    SitePoint Member
    Join Date
    Dec 2004
    Location
    Las Vegas, NV
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    it is probably wierd a creates a bit of complexity but it's not totally rediculous as my search is working just fine:

    'number' = only that number in the field
    '%,number,%' = that number in the middle of a list of 3 or more
    'number,%' = that number at the start of a list of 2 or more
    ',%number' = that number at the end of a list of 2 or more

  10. #10
    ☆★☆★ silver trophy vgarcia's Avatar
    Join Date
    Jan 2002
    Location
    in transition
    Posts
    21,235
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by m00nbeast
    it is probably wierd a creates a bit of complexity but it's not totally rediculous as my search is working just fine:
    Why deal with that complexity when everyone is beating you over the head with the easiest solution?

  11. #11
    SitePoint Enthusiast niardica's Avatar
    Join Date
    Sep 2002
    Location
    CA
    Posts
    34
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    m00nbeast.. maybe it would do you some good to read about database design and normalization..

    As it is, you're not even meeting the requirements of first normal form...


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
  •