SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Wizard billy_111's Avatar
    Join Date
    Jul 2009
    Posts
    1,683
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to remove duplicates SQL Server

    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:

    Code:
    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?
    Software Engineer ASP, ASP.NET, VB, PHP

    Kind regards
    Billy

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    if the id can be ignored, then just don't return it in the SELECT clause, and apply DISTINCT
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •