comma delimited string in nvarchar, matching with user id

[m00nbeast]

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.

[r937]

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

[m00nbeast]

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.

[r937]

not impossible, but slow as cold glue

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

[m00nbeast]

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

[r937]

you don't have to do this --

 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 --

 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

[m00nbeast]

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!

[niardica]

Obviously he's right.. Its rediculous to store a csv in a field and search it like that.

[m00nbeast]

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

[vgarcia]

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?

[niardica]

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...