CODEWITHSUNDEEP
sqlsql-server-2008joinleft-join
user3590485
user3590485

Reputation: 241

Remove duplicates in Left join SQL Server

I am having duplicates in end result. I tried using Distinct as well as group by clause.

Select distinct   
    Orders.OrderNumber, Tracking.TrackingID, 
    Orders.BalanceDue, Orders.Approved, Orders.Name, Orders.ShipCity ,  
    Orders.OrderDate, Orders.country, Orders.Shipping, Orders.CartId
from  
    orders
left join 
    Tracking on Orders.OrderNumber = Tracking.ordernum 
group by 
    Orders.OrderNumber, Orders.BalanceDue, Tracking.TrackingID, 
    Orders.Approved, Orders.Name, Orders.ShipCity, 
    Orders.OrderDate, Orders.country, Orders.Shipping, Orders.CartId

There are 8166 rows in Orders table and I want only that many columns in end result.

enter image description here

enter image description here

![enter image description here][3]

enter image description here

Upvotes: 0

Views: 585

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

One way to get one tracking id is to use an aggregation function and remove trackingid from the group by clause:

Select o.OrderNumber, max(t.TrackingID) as TrackingId,
       o.BalanceDue, o.Approved, o.Name, o.ShipCity,  
       o.OrderDate, o.country, o.Shipping, o.CartId
from orders l left join 
     Tracking t
     on o.OrderNumber = t.ordernum 
group by o.OrderNumber, o.BalanceDue, o.Approved, o.Name, o.ShipCity, 
         o.OrderDate, o.country, o.Shipping, o.CartId;

Upvotes: 3

Related Questions