SQL Select From Master - Detailed Tables

Question

I have two tables named t_master and t_detail.

The data in the t_detail table corresponds to the record in the main table.

T_master data

ID Brand
1  Toyota
2  Honda

Data for 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 the rows in the main table and at the same time select the part that has the last identifier (max), i.e.

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

Appreciate your help. Thank.

Answer 1

Using:

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
    )

Answer 3

, , , , :

( , 2 ) :

ID Brand
1  Toyota
2  Honda
3  Porsche
4. Volvo

In order to get the following result : 

ID  BRAND   DETID   PARENTID    MODEL
1   Toyota  3   1   Corolla 2011
2   Honda   5   2   Civic 2006
4   Volvo    -   -   -
3   Porcshe  -   -   -

Then make the following selection (a slightly different syntax for the convenience of Oracle users):

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