MySQL left join 2 tables order by count

Question

I have 2 tables as below and want to have select both of them result by count(column) but doesn't work please advise.

review table

ID | RID | Name | comment
555|3000 | John | John comment
555|3001 | Ben  | Ben comment
555|3002 | Smith| Smith comment

Likes table

U | PID
1 | 3000
2 | 3000
3 | 3000
4 | 3001


Expected result

ID | RID | Name | comment      | votes
555|3000 | John | John comment | 3
555|3001 | Ben  | Ben comment  | 1

I'm expecting the result from select * from review with count PID column from Likes table

My current query is

SELECT * , (SELECT COUNT( PID ) FROM Likes AS votes WHERE there.ID = PID)
FROM review AS there
LEFT JOIN Likes b ON there.RID = b.PID
WHERE ID =555
AND there.RID = b.PID AND votes>0
ORDER BY votes DESC

But it did not woking, please advise.

Answer 1

Since you are after on reviews with votes only, you can make your query shorter(and perhaps faster) by converting LEFT JOIN to INNER JOIN, and eliminating detection of COUNT: http://www.sqlfiddle.com/#!2/1f920/3

SELECT r.ID, r.RID, r.Name, `Comment`, COUNT(RID) as votes 
FROM review r 
JOIN Likes l ON l.PID = r.RID
WHERE r.ID = 555 
GROUP BY r.RID 
ORDER BY votes DESC

Output:

|  ID |  RID | NAME |      COMMENT | VOTES |
--------------------------------------------
| 555 | 3000 | John | John comment |     3 |
| 555 | 3001 |  Ben |  Ben comment |     1 |

Answer 2

SELECT ID, RID, Name, `Comment`, COUNT(RID) as votes 
FROM review AS there
LEFT JOIN Likes b ON there.RID = b.PID
WHERE ID = 555
AND there.RID = b.PID 
GROUP BY b.PID 
HAVING votes > 0
ORDER BY votes DESC

sqlfiddle