SELECT message.m_id, message.p_id, message.m_text, avg(rating)
FROM
(
SELECT message.m_id, message.p_id, message.m_text
            FROM message inner join about
            ON message.m_id = about.m_id
            WHERE m_type = 0 AND about.main = 'hi'

) AS tmp1
LEFT OUTER JOIN rate AS tmp2
GROUP BY message.m_id

#error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY message.m_id LIMIT 0, 30' at line 10