Soldato
I have 3 tables;
1: Contains Ticket records which include a user identifier.
2. Contains User groups in the format of groupname, user identifier.
3. Contains 3rd party name.
What’s I’m trying to do is group Tickets in table 1 based on the 3rd party name from table 3. However in order to identify the 3rd party I first need to match the 3rd party name in table 3 which will form part of the groupname from table2. That's easy enough however the additional challenge I have is that a user may exist in multiple User groups (table 2) but will always belong to the same 3rd party. Confused? I think I am.
So I’ve managed to make a start of sorts, and currently get multiple returned rows for the same ticket record from table 1. In this instance before I even attempt to match against the 3rd party I first have to remove where I get duplicate rows returned due to the user residing in more than 1 User group.
Question is how can I address that? Is there an easy approach where it finds the first match and then ignores all others?
Cheers Paul.
1: Contains Ticket records which include a user identifier.
2. Contains User groups in the format of groupname, user identifier.
3. Contains 3rd party name.
What’s I’m trying to do is group Tickets in table 1 based on the 3rd party name from table 3. However in order to identify the 3rd party I first need to match the 3rd party name in table 3 which will form part of the groupname from table2. That's easy enough however the additional challenge I have is that a user may exist in multiple User groups (table 2) but will always belong to the same 3rd party. Confused? I think I am.
So I’ve managed to make a start of sorts, and currently get multiple returned rows for the same ticket record from table 1. In this instance before I even attempt to match against the 3rd party I first have to remove where I get duplicate rows returned due to the user residing in more than 1 User group.
Code:
SELECT DISTINCT
TOP (100) PERCENT dbo.es_ug.name, dbo.ra.REQUESTER_LOGIN_NAME_, dbo.ra.TICKET_ID_, dbo.ra.ASSIGNED_TO_INDIVIDUAL_, dbo.ra.PRIORITY, dbo.ra.TYPE,
dbo.ra.SUB_CODE, dbo.ra.SUMMARY
FROM dbo.es_ug INNER JOIN
dbo.ra ON dbo.es_ug.account_name = dbo.ra.REQUESTER_LOGIN_NAME_
WHERE (dbo.ra.STATUS < 4) AND (dbo.ra.ASSIGNED_TO_GROUP_ = 'Some_Group_Name')
ORDER BY dbo.ra.TICKET_ID_
Cheers Paul.