I will try to explain much as possible. here is my query..
SELECT * FROM mm_star_ratings s
JOIN mm_posts p ON s.post_id = p.postid
WHERE p.type='B'
ORDER BY s.rating DESC LIMIT 5
type='B' is the main post and type='C' is the reply on that post. There is a parentid column in mm_posts to set main post (B) id for C. Do I want to get the title from mm_posts if type='C' is there anyway?
The challenge for me is to get it work with my rating table where post_id is set for both B and C
You can use Following query,
SELECT s.*,p.*, IF(p.type='C',pp.title,p.title) as title FROM mm_star_ratings s
JOIN mm_posts p ON s.post_id = p.postid Left Join mm_posts pp on pp.postid=p.parent_id
ORDER BY s.rating DESC LIMIT 5