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:
Now depending on the @SearchStr, this could also return multuple different records so it could even be something like this:
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?