Distinct in Join Query

February 8, 2023

in the below Query Benz table car_no is repeated. when I join and compare with other table car_no how do use distinct?

table 1: Cars
Table 2: benz

  join Benz bz on bz.car_no = cr.car_no

in the above car_no values in the Benz table are repeated. in Cars table car_no values unique. then how to join with distinct in query.

something i tried.

  join Benz bz on Distinct(bz.car_no) = cr.car_no

getting error.

>Solution :

Please try this

SELECT cr.*, bz.*
FROM Cars cr
JOIN (
  SELECT DISTINCT car_no, other_columns
  FROM Benz
) bz
ON bz.car_no = cr.car_no;

In this example, the subquery ‘SELECT DISTINCT car_no, other_columns FROM Benz’ is used to select only the distinct ‘car_no’ values from the ‘Benz’ table. The subquery is then joined with the ‘Cars’ table using the ‘ON’ clause. The resulting query will only return rows where the ‘car_no’ values are unique.