Messaging:- Need to find last child of its parent with in the same table

Question

I am making a messaging module.

I need to find the all parents with their last child message.

Like:

+--------------------+----------------------+---------+
| pkUserDiscussionID | fkDiscussionParentID | message |
+--------------------+----------------------+---------+
|                  1 |                    0 | a       |
|                  2 |                    1 | b       |
|                  3 |                    1 | c       |
|                  4 |                    0 | a       |
+--------------------+----------------------+---------+

I need to find the last child of parent node (fkDiscussionParentID=0) with last child (pkUserDiscussionID=1 last child pkUserDiscussionID=3) with message.

Below I found a link close to my answer but not completely:

COUNT of rows with parent id equal to the row we have with a parent id of 0 in one query

score 1 · Accepted Answer · edited Oct 23 '15 at 04:59

Because of my hard work I got the answer:-

SELECT 
    parent.pkUserDiscussionID, 
    child.UserDiscussionMessage AS childmsg, 
    parent.UserDiscussionTitle, 
    parent.UserDiscussionMessage AS parentmsg 
FROM user_discussion parent 
LEFT JOIN user_discussion child 
    ON child.fkDiscussionParentID = parent.pkUserDiscussionID 
    AND child.pkUserDiscussionID = 
    (
        SELECT max( pkUserDiscussionID ) 
        FROM user_discussion AS c 
        WHERE c.fkDiscussionParentID = parent.pkUserDiscussionID 
    )
WHERE 
    parent.fkDiscussionParentID =0 
GROUP BY 
    parent.pkUserDiscussionID 
ORDER BY 
    parent.pkUserDiscussionID ASC 
LIMIT 0 , 30

Can anyone suggest anything better than this?

Messaging:- Need to find last child of its parent with in the same table

1 Answers1