How to get unique values in multiple left join

left join without duplicates from right table
avoid duplicate rows in sql join
left join duplicate rows
how to eliminate duplicate rows in inner join
sql left join multiple tables
sql remove duplicate rows after join
sql remove duplicate columns after join
left join distinct

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   

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

SQL Left Join: A Distinct Trick to Multi-User Alerting via SQL, An SQL Left Join trick allowed us to implement multi-user alerting. multi-user alerting also makes it possible for an incident to have multiple acknowledgers. the return value is a map of incident id to count for that incident. SELECT t1.id FROM tbl t1 LEFT JOIN tbl t2 on t1.type = t2.type AND t1.dtIn = t2.dtIn GROUP BY t1.id, t2.dtOut ORDER BY t2.dtOut However, as I mentioned above this can open the possibility of having the same id listed more than once, if it matches to more than record on the right-side table.

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

Joining multiple tables results in duplicate rows, Presumably, you want to see a single entry for each unique person/address/​email/website combination. If so, try this: SELECT (person.FirstName + ' ' + person. Summary: in this tutorial, we will introduce you another kind of joins called SQL LEFT JOIN that allows you to retrieve data from multiple tables. Introduction to SQL LEFT JOIN clause In the previous tutorial, you learned about the inner join that returns rows if there is, at least, one row in both tables that matches the join condition.

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

Grouping results to get unique rows after multiple joins, If you just want to return one row per story rather than a set, you can use MIN() or MAX() (and a GROUP BY) or SQL Server's TOP 1 function within a subquery to  If you want to do it without exists: SELECT C.CustomerID, C.CustomerName, sum(kp.id) FROM Customers c join KP_Customers_Products kp on c.id = kp.CustomerId Group by C.CustomerID, C.CustomerName Having (Sum(Kp.id) > 0) ORDER BY C.LastName; This should give you the unique customer's that has a product according to the product-relation table.

SQL Left Join, The SQL Left Join or simply LEFT JOIN return all rows from the first table listed after Example of SQL Left Join using multiple columns come, but for the unmatched rows of company table, the column value will be NULL. INSERT INTO /*+ APPEND*/ TEMP_CUSTPARAM(CUSTNO, RATING) SELECT DISTINCT Q.CUSTNO, NVL(((NVL(P.RATING,0) * '10.0')/100),0) AS RATING FROM TB_ACCOUNTS Q LEFT JOIN TB_CUSTPARAM P ON P.TEXT_PARAM IN (SELECT DISTINCT PRDCD FROM TB_ACCOUNTS) AND P.TABLENAME='TB_ACCOUNTS' AND P.COLUMNNAME='PRDCD';

Oracle SELECT DISTINCT By Practical Examples, In this statement, the values in the column_1 of the table are compared to determine the To retrieve unique data based on multiple columns, you just need to specify the To get unique contact first names, you add the DISTINCT keyword to the INNER JOIN · LEFT JOIN · RIGHT JOIN · FULL OUTER JOIN · CROSS JOIN  LEFT JOIN three tables Show maximum mark with student details and house name SELECT a.id,a.name,a.class,b.mark,c.h_name FROM `student6` a LEFT JOIN (SELECT student_id, MAX(mark) as mark FROM student_mark GROUP BY student_id ) b on a.id= b.student_id LEFT JOIN student_house c on a.house_id=c.house_id

Oracle LEFT JOIN By Practical Examples, This tutorial shows you how to use the Oracle LEFT JOIN clause to query data from In case a row in the T1 table does not have any matching row in the T2 table, the query combines column values from Oracle LEFT JOIN – join multiple tables SELECT · Oracle DUAL Table · ORDER BY · SELECT DISTINCT · WHERE  Multiple joins can be described as follows; multiple join is a query that contains the same or different join types, which are used more than once. Thus, we gain the ability to combine multiple tables of data in order to overcome relational database issues.

Comments
  • 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 minimal reproducible example before you start!
  • Which 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 for the database product you are using
  • Please provide sample data and desired results. Which values do you want when there are duplicates?
  • I have added the results. I use SQL server.
  • Hei, thanks for your answer. I tried it and got this error: The multi-part identifier "Dest.Code" could not be bound.
  • Try my edited answer.I removed base. from the query.
  • 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?
  • i'm not getting ...what are you trying to say..?
  • 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!
  • OK. But when I run the code i get an error: Incorrect syntax near the keyword 'on'.
  • 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...