Hey everyone,
I was originally going to take care of this in the ASP code but i think its better to do it in the stored procedure instead. Basically i have the following statement:
DECLARE @AgentID int = 515, @SearchStr varchar(max) = 'CHLI'
SELECT DISTINCT Q.ID,
CASE
WHEN EXISTS
(
SELECT Q1.SID
FROM Quote Q1
LEFT JOIN Transport T1 ON Q1.ID = T1.QuoteID
LEFT JOIN Route R1 ON T1.RouteID = R1.ID
INNER JOIN Lookup L1 ON Q1.TID = L1.ID
WHERE Q1.Code = Q.Code AND Q1.SID = 2500
AND Q1.Depart >= GETDATE()
AND Q1.Code LIKE ISNULL('%' + NULLIF(@SearchStr,'') + '%','%')
AND Q1.ID NOT IN (SELECT QuoteID FROM TourAgent WHERE AgentID = @AgentID)
AND (L1.Name <> 'Pot' OR R1.Code = '')
)
THEN Q.Code + '*' ELSE Q.Code
END AS Code
FROM Quote Q
LEFT JOIN Transport T ON Q.ID = T.QuoteID
LEFT JOIN Route R ON T.RouteID = R.ID
INNER JOIN Lookup L ON Q.TID = L.ID
WHERE Q.Depart >= GETDATE()
AND Q.SID IN (2500, 2550, 2540)
AND Q.Code LIKE ISNULL('%' + NULLIF(@SearchStr,'') + '%','%')
AND Q.ID NOT IN (SELECT QuoteID FROM TourAgent WHERE AgentID = @AgentID)
AND (L.Name <> 'Pot' OR R.Code = '')
GROUP BY Q.Code, Q.ID, Q.SID
ORDER BY Code
This returns the following:
91978 CHLIB0211*
91979 CHLIB0211*
91980 CHLIB0211*
91981 CHLIB0211*
91982 CHLIB0211*
Now depending on the @SearchStr, this could also return multuple different records so it could even be something like this:
91978 CHLIB0211*
91979 CHLIB0211*
91980 CHLIB0211*
91981 CHLIB0211*
91982 CHLIB0211*
91983 PEPPM0311*
88692 4RIA1111
88668 4RIA911
88669 4RIA911
88806 4RIV1111
88819 4RIV911
88725 4RIV911
88729 4RIV911
Now i know there is an ID but that can be ignored. So basically what i need to do is simply remove duplicate names regardless of the ID it has as that is controlled elsewhere.
Can i do this?