comma delimited string in nvarchar, matching with user id

comma delimited matching with number

[m00nbeast]

Okay heres the deal (I hope I can explain this clearly enough while being as breif as I can):

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.

[aspdaddy]

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

You should have added a usertickets table tothe database to sit between users and tickets as it had become a many-to-many.

Anyway you could still fudge it using IN:
session("user_id") IN (t.assigned_to)

[m00nbeast]

oh thats a great idea! I'll have to give that a shot! thanks a million!