SQL Select From Master - Detail Tables

Question

I have two tables, named t_master and t_detail.

The data in the t_detail table corresponds to a record on the master table.

Data of t_master

ID Brand
1  Toyota
2  Honda

Data for the t_detail

DetID ParentID Model
1     1        Corolla 2009
2     1        Corolla 2010
3     1        Corolla 2011
4     2        Civic 2004
5     2        Civic 2006

Now, I want to make a query that should select all rows in the master table and at the same time, select the detail which has the latest ID (max), i.e.

ID Brand  DetID ParentID Model
1  Toyota 3     1        Corolla 2011
2  Honda  5     2        Civic 2006

Appreciate your help. Thanks.

Answer 1

Use:

SELECT m.id, 
       m.brand,
       x.detid,
       x.parentid,
       x.model
  FROM T_MASTER m
  JOIN T_DETAIL x ON x.parentid = m.id
  JOIN (SELECT d.parentid,
               MAX(d.detid) AS max_detid
          FROM T_DETAIL d
      GROUP BY d.parentid) y ON y.max_detid = x.detid
                            AND y.parentid = x.parentid

Answer 2

SELECT Make.ID, Make.Brand, Model.DetID, Model.Model
FROM t_master Make
     INNER JOIN t_detail Model ON Make.ID = Model.ParentID
WHERE
    DetID = 
    (
       SELECT MAX(DetID) From t_detail WHERE ParentID = Make.ID
    )