Mysql – Dependent MySQL SELECT

dependenciesgreatest-n-per-groupMySQLselect

My problem is the following:

My tables are MESSAGE and MESSAGE_COMMENT,

MESSAGE (id,content)

MESSAGE_COMMENT (id, message_id, content)

I need to select all messages and max 3 comments for each message, like in this example:

type | id | content
M      15   "this is a message with no comments"
M      16   "this is another message with many comments"
R      16   "comment1"
R      16   "comment2"
R      16   "comment3"
M      17   "this is another message with no comments"

"id" is MESSAGE.id when it's a message and COMMENT.message_id when it's a comment.

I hope I have clearly explained my problem..

Best Answer

SELECT  *
FROM    (
        SELECT  m.id,
                COALESCE(
                (
                SELECT  id
                FROM    message_comment mc
                WHERE   mc.message_id = m.id
                ORDER BY
                        mc.message_id DESC, id DESC
                LIMIT 2, 1
                ), 0) AS mid
        FROM    message m
        ) mo
LEFT JOIN
        message_comment mcd
ON      mcd.message_id >= mo.id
        AND mcd.message_id <= mo.id
        AND mcd.id >= mid

Create an index on message_comment (message_id, id) for this to work fast.

See this article in my blog for more detailed explanation of how this works:

Related Topic