2

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

1

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?

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.