How to get unique values in multiple left join

Question

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

Simpify your problem. (Remove some columns not needed to illustrate the issue.) Show us some sample table data and the expected result - all as formatted text, no images. Take a look at [mcve] before you start! — jarlh, Sep 19 '19 at 08:57
Which [DBMS](https://en.wikipedia.org/wiki/DBMS) product are you using? "SQL" is just a query language, not the name of a specific database product (and your query is invalid standard SQL). Please add a [tag](https://stackoverflow.com/help/tagging) for the database product you are using — , Sep 19 '19 at 10:40
Please provide sample data and desired results. *Which* values do you want when there are duplicates? — Gordon Linoff, Sep 19 '19 at 11:15

forpas · Accepted Answer · 2019-09-20T08:34:15.950

3

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

edited Sep 20 '19 at 08:34

answered Sep 19 '19 at 13:54

forpas

160,666
10
38
76

Hei, thanks for your answer. I tried it and got this error: The multi-part identifier "Dest.Code" could not be bound. – db_noob Sep 20 '19 at 07:53
Try my edited answer.I removed `base.` from the query. – forpas Sep 20 '19 at 08:34

THE LIFE-TIME LEARNER · Answer 2 · 2019-09-19T10:50:26.577

1

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...

edited Sep 19 '19 at 10:50

answered Sep 19 '19 at 09:02

THE LIFE-TIME LEARNER

1,476
1
8
18

Thanks for your answer, it make sense however I am using SQL server and it does't allow me to use DISTINCT ON, should it? – db_noob Sep 19 '19 at 09:44
i'm not getting ...what are you trying to say..? – THE LIFE-TIME LEARNER Sep 19 '19 at 09:47
`DISTINCT` is **not a function**. It's a part of `SELECT DISTINCT` and works on the whole selected rows. Remove those redundant parentheses and simply write `SELECT DISTINCT Code, CityId FROM ...` to make code clearer! – jarlh Sep 19 '19 at 10:04
OK. But when I run the code i get an error: Incorrect syntax near the keyword 'on'. – db_noob Sep 19 '19 at 10:32
I have edited my answer ...this will work for you....definitely.. by mistake i have added this line ON dest1.CityId=Dest.CityId this line... – THE LIFE-TIME LEARNER Sep 19 '19 at 10:51
ON after SELECT DISTINCT – db_noob Sep 19 '19 at 11:04
The code works. But doesn't give the desired result. I have edited the original post. Could you take a look at it? – db_noob Sep 19 '19 at 12:32
Left join will always pick all value of left table...so your might be wrong O/ P just because this...i'm not totally assure?? – THE LIFE-TIME LEARNER Sep 19 '19 at 12:52
This code will not eliminate the duplicates that are created from left joins and will not answer this question. – avery_larry Sep 19 '19 at 15:46

score 0 · Answer 3 · answered Sep 19 '19 at 16:15

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,'').

How to get unique values in multiple left join

3 Answers3