I need help with joining three tables, this image shows the fields and table names.
Extract from database:
voting table
id name closed date votes_up votes_down
4 29192 0 1467441761 32 14
21 14427 0 1467450299 1 0
20 14464 0 1467449751 0 0
14 27345 0 1467447075 0 0
9 27329 0 1467443096 1 0
games table
id id_gamepressure id_gamepressure2 title
256 8228 10759 Yu-Gi-Oh! World Championship 2007
512 2520 9294 Boulder Dash Rocks!
768 10155 12664 SpongeBob vs. The Big One. Beach Party Cook-Off
1024 9587 8484 Nacho Libre
images table
id id_game id_gamepressure filename
30695 10001 173814 10001_deca-sports_11792.jpg
30690 10001 173819 10001_deca-sports_1524.jpg
30692 10001 173817 10001_deca-spoets_3551.jpg
30694 10001 173815 10001_deca-sportss_572.jpg
30693 10001 173816 10001_deca_sports_8866.jpg
30691 10001 113818 10001_deca-sports_9417.jpg
Basically I want to be able to find the most popular games via from the "voting table" using the field "votes_up" then linking matching it with the "games table" and finally with the "images table"
I want to display it in list form, sorted by the highest voted game.
like this:
Something like this, I dont know the actual table names from your image. Or what fields you want.
SELECT
g.id,
i.id //or whatever fields you want.
FROM
games AS g
INNER JOIN
images AS i
ON
g.id = i.id_game
LEFT JOIN
votes AS v
ON
g.id = v.name
ORDER BY
v.votes_up
DESC
In this case as votes is already a sum you dont have to count them like I was thinking, ( votes would be 1 row per vote, that is what I was thinking )
Id use a left join, but I supose as you want the most up voted game it wouldn't really matter.
make sure the ids are indexs as well as the vote counts or sorting will kill the performance.