MySQL - Order results by relevancy, LEFT JOINS and more
- by XaviEsteve
Hi guys,
I am trying to get some results ordered by total votes (where client votes count 2 points and other people votes are 1 point).
tab_names:
+-----------+
| Name | id |
+------+----+
| John | 1 |
| Paul | 2 |
+------+----+
tab_votes:
+--------+-----------+
| idname | ip |
+--------+-----------+
| 2 | 127.0.0.1 |
| 2 | 127.0.0.1 |
| 2 | 82.23.5.1 |
| 1 | 127.0.0.1 |
+--------+-----------+
This is the MySQL query I've got but doesn't work:
SELECT *
COUNT(v.idname) AS totalvotes,
(SELECT COUNT(v.ip) FROM tab_votes WHERE v.ip LIKE '$ip') AS uservotes
FROM tab_names n
LEFT JOIN tab_votes v ON n.id = v.idname
GROUP BY n.name
ORDER BY uservotes DESC, totalvotes DESC
LIMIT 40
