I have 3 tables as follows
Now I want all questions of a particular category and its one answer if exits which has highest rate and username of that person who has answered.
I am using following query to do this but it is not giving me that answer which has highest rate.
from users d
FROM `questions` a
left join answers b
WHERE a.`cat_id` = 8
group by b.q_id) c
What if try this:
SELECT c.*,d.username,d.user_id FROM users d JOIN ( SELECT b.*,a.question FROM `questions` a LEFT JOIN ( SELECT t1.* FROM answers t1 JOIN ( SELECT q_id, max(rate) AS rate FROM answers GROUP BY q_id ) t2 ON t1.q_id = t2.q_id AND t1.rate = t2.rate ) b ON a.q_id=b.q_id WHERE a.`cat_id` = 8 GROUP BY b.q_id) c ON c.user_id=d.user_id
Cause you have not provided some sample data and desired result, so this may not work as you expected.