SQL and grouping

[darkgen]

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.

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_

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.

 

[darkgen]

So the original query throws out the following (sample data);

As you can see it returns multiple rows as expected since the user resides in 4 usergroups (es_ug table 2).

Table 1 contains all unique rows (Ticket_ID_)

Sample of data from es_ug (table 2);

The 3rd table literally contains a single data column with a list of 3rd parties. For instance this maybe "Satyam".
So ultimately I want to first identify which group the user (REQUESTOR_LOGON_NAME_) belongs to (if it's multiple then the first match) and group the tickets according to the chosen 3rd party which might be "Satyam" for instance. So in essence there are two processes at play both separate.

Firstly a query to match a 3rd party to the user (1st match will suffice) and output the data based on 1 row per TICKET_ID_. This is what I'm struggling with.
Secondly a mechanism to use the first query with a where condition based on specific 3rd party. This is something I'm probbaly OK to do.

Probably clear as mud and yes this is a cack handed way to do this I'm sure!

Help! Paul.

 

[darkgen]

This should work I think:

SELECT [ThirdPartyUsers].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.ra INNER JOIN
(SELECT dbo.es-ug.account_name
		, dbo.es-ug.name
		, [ThirdPartyName] 
	FROM dbo.es_ug INNER JOIN 
	[ThirdPartyTable] ON [ThirdPartyTable].Whatever = dbo.es_ug.Whatever) AS [ThirdPartyUsers] ON dbo.ra.REQUESTER_LOGIN_NAME_ = [ThirdPartyUsers].account_name

WHERE (dbo.ra.STATUS < 4) 
AND (dbo.ra.ASSIGNED_TO_GROUP_ = 'Some_Group_Name')

ORDER BY dbo.ra.TICKET_ID_

I've basically joined the users to the third party table in a subquery (you might need a GROUP BY in there, not sure without seeing your data.

This should remove the need for DISTINCT and unless I'm missing something, TOP 100 PERCENT is just selecting all records? (I'm assuming this is T-SQL)

Apologies for the awful indentation - I was trying to make it easier to see in the CODE window!

No thank you very much, it's giving me something to work with albeit I haven't got it working as yet but I think I can understand the logic. Just need to fiddle with it!

 

[darkgen]

Ooh Trucamo, that seems rather quite elegant and looks like with a lttle tweak is reporting what I expect. Need to sanity check the data some more but this looks good.

Many many thanks!

cheers, Paul.