开发者

SQL Select From Master - Detail Tables

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 Bra开发者_开发百科nd
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.


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


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
    )


Please note that if you have more rows in Master Table and there are no rows in Detail Table that fits , but you still want to show ALL master table rows then you should initiate the below :

( Please note that you have to add 2 more rows to the master table) for example :

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 do the following select (a bit different syntax for the ease 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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜