I tried posting this to the SQL Server programming group, but then thought
more about it, and thought it was more of an ASP question.
If you want the original long description, it's below. But the short version
is, I have created a new table to resolve a many-to-many relationship in my
tech support ticket-tracking app. When I do a query which should yield me
several tickets, it gives me more than one row of the same ticket if that
ticket happens to have more than one row in the new table I just mentioned.
I'd like it to only give me one, but apparently, that isn't going to happen.
So my ASP question is, how can I tell it not do display a row from the
recordset if there is already a row with that same ticket number?
Here's the long version, posted on the SQL group.
=============================================================
First, here's the query:
SELECT T.TKT_TICKETID, T.Orig_Source,T.Contact_CustID,T.orig_CustID AS
OrigCust,
T.Orig_TimeStamp, T.Actual_TimeStamp, MAX(H.TIMESTMP)AS
HistTime,T.BriefDesc,
T.LongDesc,T.InternalDesc,T.Environment,T.AssignedEmployee,T.StatusID,T.Curr
ent_Source,
T.CustomerCode AS CustCode,T.DefectNum,E.FirstName, E.LastName,
S.Description,
S.NameShort AS StatusShort,ISNULL(CU.CustomerName, 'ARGO') AS CustomerName,
ISNULL(CU.NameShort, 'ARGO') AS NameShort,T.LastModified,T.CustTicketID,
TA.EmployeeID AS AssistID
FROM dbo.TKT_TICKET T
INNER JOIN dbo.EMPLOYEE E ON T.AssignedEmployee = E.EmployeeID
LEFT OUTER JOIN dbo.TKT_ASSIST TA ON T.TKT_TicketID = TA.TicketID
INNER JOIN dbo.TKT_STATUS S ON T.StatusID = S.StatusID
LEFT OUTER JOIN dbo.CUSTOMER CU ON CU.CustomerCode = T.CustomerCode
LEFT OUTER JOIN dbo.TKT_HISTORY H ON T.TKT_TICKETID = H.TICKETID
WHERE T.StatusID IN ('2','3','4','5','6','7')
AND T.AssignedEmployee = '200'
GROUP BY T.TKT_TICKETID, T.Orig_Source, T.Contact_CustID,T.Orig_CustID,
T.Orig_TimeStamp, T.Actual_TimeStamp,T.ADSVersion,T.BriefDesc,T.LongDesc,
T.Environment, T.AssignedEmployee,
T.StatusID,T.Current_Source,T.InternalDesc,
T.orig_CustID,T.Current_Source,T.CustomerCode,T.LastModified,T.CustTicketID,
T.DefectNum,E.FirstName, E.LastName,
S.Description,S.NameShort,CU.CustomerName,
CU.NameShort, TA.EmployeeID ORDER BY Orig_TimeStamp
=====================================
Problem Description: As you can see, TKT_Ticket is the main table. I just
created a table called TKT_ASSIST, and it only has the ticket id plus the
employee id, since there can be more than one employee per ticket, and more
than one ticket per employee. I then added to the existing query the last
item you see in the select part, "TA.EmployeeID AS AssistID(plus the
matching part in the GROUP BY)" and then there's a join for the 2 tables.
The problem is, when it is run, the query yields more than one row per
ticket, if that ticket happens to have more than one row in the TKT_Assist
table. In other words, the Assist table was put there to resolve a
many-to-many relationship, but it is still showing me "many" rows of tickets
when one will do.
Here's what is returned (with some stuff smipped for brevity):
15882 03/15/2004 Test ARGO RSCH Possible Memory Leak in Testing
Environment
15913 03/18/2004 Prod CUST RSCH Share Pool errors experienced after
converting from NT to W2000
15998 04/02/2004 Train CUST RSCH HTTP Post Communication Error
15998 04/02/2004 Train CUST RSCH HTTP Post Communication Error
Note that Ticket 15998 is there twice. Sure enough, when I go into the
Assist table, that's the only one with two rows, i.e. two employees attached
to it.
So what adjustments do I need to make to the SQL statement to cause it to
return only one row per ticket? I tried putting the word DISTINCT right
after the word SElECT, but that didn't chagne anything. I changed the INNER
JOIN to an outer one, but that just kept some other tickets off of the list,
and ticket 15998 still showed up twice.