CODEWITHSUNDEEP
sql-serverfilterunique-values
db_noob
db_noob

Reputation: 135

How to get unique values in multiple left join

I have the following code I have to join 3 tables but Dest.Code has has repeated values. How can I get unique values for Dest.Code?

I have tried DISTINCT but doesn't work.

SELECT
   Dest.Code 
  ,City.CityName  
  ,Country.Id
FROM base.Dest
   Left join base.City
   On base.Dest.CityId = base.City.Id
   Left join base.Country 
   On base.City.CountryId = base.Country.Id

This is the result: But ASA appears twice. I need it only once doesn't matter which one.

Code    CityName    Id          
ASA     Ardmore     1E-599-4E   
ASA     Miami       8B-203-4D   
WBG     Rome        9S-893-2E   
BMU     Leon        2B-103-8E

Upvotes: 0

Views: 3927

Answers (3)

forpas
forpas

Reputation: 164174

Wrap your query inside a CTE with a new column that you will use to filter the results.
This new column is produced with ROW_NUMBER() window function partitioned by Dest.Code: 

WITH cte as (
  SELECT Dest.Code, City.CityName, Country.Id,
  ROW_NUMBER() OVER (PARTITION BY Dest.Code ORDER BY City.CityName, Country.Id) AS rn   
  FROM Dest
  LEFT JOIN City ON Dest.CityId = City.Id
  LEFT JOIN Country ON City.CountryId = Country.Id
)
SELECT Code, CityName, Id
FROM cte
WHERE rn = 1

Upvotes: 3

avery_larry
avery_larry

Reputation: 2135

I like to think of this as a creative alternative to CTE or anything using row numbers, but I have no idea on performance:

SELECT
   Dest.Code 
  ,max(City.CityName + ' ### ' + Country.Id)
FROM base.Dest
   Left join base.City
   On base.Dest.CityId = base.City.Id
   Left join base.Country 
   On base.City.CountryId = base.Country.Id
group by dest.code

This has the problem of presenting CityName and Country.Id as a single output column. This may be acceptable -- or you can use patindex, left, and substring to split it back out:

SELECT
   Dest.Code 
  ,left(max(City.CityName + ' ### ' + Country.Id),patindex('% ### %',max(City.CityName + ' ### ' + Country.Id))) CityName
  ,substring(max(City.CityName + ' ### ' + Country.Id),patindex('% ### %',max(City.CityName + ' ### ' + Country.Id)) + 5,len(max(City.CityName + ' ### ' + Country.Id))) Id
FROM base.Dest
   Left join base.City
   On base.Dest.CityId = base.City.Id
   Left join base.Country 
   On base.City.CountryId = base.Country.Id
group by dest.code

It can get a little messy/difficult to understand. Additionally, the (arbitrary) string added between CityName and Country.Id MUST BE KNOWN TO NOT EXIST IN CITYNAME. Finally, the code I present does not properly account for any null values. I would replace every instance of City.CityName and Country.Id with isnull(City.CityName,'') and isnull(Country.Id,'').

Upvotes: 0

THE LIFE-TIME LEARNER
THE LIFE-TIME LEARNER

Reputation: 1522

SELECT
   Dest.Code 
  ,Dest.DestName 
  ,Dest.Code + ' ' + Dest.DestName as Destination
  ,Dest.Latitude
  ,Dest.Longitude
  ,Dest.CityId
  ,City.InternCityName 
  ,City.CityName 
  ,Country.CountryNameInt 
  ,Country.CountryName 
  ,Case 
     when Country.CountryName = 'Country' 
     then 'Local' 
     else 'Exterior' 
     end 
FROM DataBase.Destinations as Dest
INNER JOIN
(
    SELECT  DISTINCT Code,CityId FROM  DataBase.Destinations AS dest1 
)dest2

ON dest.CityId=dest2.CityId

Left join DataBase.Cities City
On Dest.CityId = City.Id

Left join DataBase.Countries Country
On City.CountryId = Country.Id

Note:- Sub Query is using for distinct value and inner join for common value only...

Upvotes: 1

Related Questions