digicom digicom - 3 months ago 10
SQL Question

MySQL left join with max id

I Have a table called tbl_messages the containes messages. Each message has a value of 0 under MessageReply if it's the first message and a message id if it is a reply to another message.

I need to get for each root message it's data + the date of it's latest response.

I'm running the following SQL procedure:

SELECT
m1.*, m2.MessageID, m2.MessageDate
FROM
tbl_messages as m1 LEFT JOIN tbl_messages as m2
ON
m1.MessageID = m2.MessageReply
WHERE
m1.MessageReply = '0' AND
(m1.MessageUser = '1' OR m1.MessageBusiness = '1') AND
m2.MessageID = (SELECT MAX(MessageID) FROM tbl_messages WHERE MessageReply = m1.MessageID)
ORDER BY
m2.MessageID desc, m1.MessageID desc


My problem is the because of the last WHERE statement it doesn't return values from the left table that doesn't have values on the right table.

How can I solve it?

Answer

When you write the statement in WHERE Clause, it will filter the whole query results.

And when you write that statement in ON Clause when joining left, it will only filter left joined table and result will be NULL values from secondary table if statement is not matched.

SELECT 
    m1.*, m2.MessageID, m2.MessageDate 
FROM 
    tbl_messages as m1 LEFT JOIN tbl_messages as m2 
ON 
    m1.MessageID = m2.MessageReply 
    AND
    m2.MessageID = (SELECT MAX(MessageID) FROM tbl_messages WHERE MessageReply = m1.MessageID)

WHERE 
    m1.MessageReply = '0' AND 
    (m1.MessageUser = '1' OR m1.MessageBusiness = '1') 
ORDER BY 
    m2.MessageID desc, m1.MessageID desc