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.

[NogDog]

Is adding a new table to the database an option? If so, then you could create a ticket-to-user table which would simply have a column for ticket ID and one for user ID. You would insert a record for each user assigned to a given ticket. Then the query would be something like:

SELECT t.ticket_id as [Ticket ID], t.priority, t.subject, b.name as [Client]
FROM tickets t, users u, business b, ticket_to_user as tu
WHERE u.user_id = tu.user_id and t.ticket_id = tu.ticket_id and
t.assigned_to = "& session("user_id")&" and t.status = 'In Progress' and
b.business_id = t.business_id"

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

btw - thanks for taking the time to help me, I really appreciate it.

[chazzy]

You're trying to perform an unindexable (i feel like that's a bad word, whatever) which is typically not recommended. If you go back to your original DBMS, you'll remember something called a multivalued field. Those always got turn into their own tables.

Plus, writing something to change who the associated person is, remove a person, etc, is much more difficult than simply inserting and updating and deleting. You perform a query to get the result, then you have to search the result to find the person.

You are better off converting to a separate table. you could, in theory, create a view to replace your original queries though.

[m00nbeast]

Thanks for the suggestion. I may try that if I cannot figure this out the way it is currently set up. I really don't want to go redo all of the work I just redid.

The changing of the assigned people is already done in the ticket view/ticket update.

Perhaps I may be able to get it done by selecting all open tickets (as there really shouldn't be that many open at once *about 40 at most*) and then in my ASP code, filter out the result set I want.

I'll keep at it. If anyone thinks of a solution, or can see where I went wrong in my SQL code above, I would greatly appreciate the help, but if you gurus don't have the answer then a n00bert like me will most likely have to take the road you have already suggested.

[NogDog]

OK, this is ugly, and I'm sure there's a better way, but I think it would work:

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 (t.assigned_to='"& session("user_id") &"' OR t.assigned_to LIKE '%,"& session("user_id") &",%' OR t.assigned_to LIKE '"& session("user_id") &",%' OR t.assigned LIKE '%,"& session("user_id") &"') and t.status = 'In Progress' and b.business_id = t.business_id order by b.name, t.ticket_id"

[m00nbeast]

I had just figured it out - it looks like we came to the same conclusion:

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'm sorry for wasting your time but truely do appreciate the help you gave!

[chazzy]

Just a quick question - maybe you've already thought about it but figured I'd throw it out there too.

Let's say you have a tech with ID 1 and a tech with ID 11. How will you distinguish, in a list, between those elements assigned to 1 and those assigned to 11 without 1 seeing 11's? if you just do %1% you will get 1 associated with 10,11,21,101, etc.

[NogDog]

That's why we included the delimiting commas in the various LIKE comparisons:

'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

[chazzy]

well that's the thing, you're doing OR's. that's just like saying:
if 1 is in the field or 1 is after a comma or 1 is before a comma or 1 is between two comma's.

so if your field contained this list:
(2,3,10,21)
and you're userid 1, you would see this because 1 is after a comma OR 1 is before a comma OR 1 is between two comma's.

Edit: nevermind, realized i read the %'s wrong, not in the order they were in.

And reedit: no, I didn't. it's late for me too, i was reading %,number,% as ,%number%, so no, it's fine.

[m00nbeast]

what he said :D

[m00nbeast]

it's too late, I can't decide if you figured out a real flaw in teh code or not

[NogDog]

For instance:

'number,%' : since no leading wildcard, would only match if the exact number were at the start of the list and followed immediately by a comma.

[m00nbeast]

sheeyat
yer right I have too many wildcards!

someone suggested I do this:


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