LEFT OUTER JOIN with no duplicates on left table

Question

I've got two tables (let's say "left" and "right" tables). The right table's got from none to several rows matching each row on the left table.

I need to perform a query where I can join the two tables the same way a LEFT OUTER JOIN works, but getting only one row for each existing one in the left table. That row should be the one corresponding to the highest id on the right table.

A NATURAL JOIN would work, but I won't be getting the rows from the left table that don't match any row on the right one.

Answer 1

You can use GROUP BY clause to show only distinct records

Answer 2

This is what I would do:

select * 
  from `left` as l 
    LEFT JOIN `right` as r 
       on (l.id = r.left_id) 
group by l.id 
order by r.id

Answer 3

Try this:

 Select L.[ValuesfromLeftTable], ...
        R.[ValuesfromRightTable], ...
 From LeftTable as L
   Left Join RightTable as R
     On R.Id = (Select Max(id) 
                From RightTable
                Where FK = L.Id)

Answer 4

SELECT Left.Field1, Left.Field2, ... Right.Field1, Right.Field2, ...
FROM tblLeft AS Left
LEFT JOIN (SELECT CommonID, Max([ID]) AS MaxID
    FROM tblRight
    GROUP BY CommonID) AS RightMax ON Left.CommonID = RightMax.CommonID
LEFT JOIN tblRight AS Right ON RightMax.MaxID = Right.[ID] AND Left.CommonID = Right.CommonID