Banana Banana - 2 months ago 15
MySQL Question

Select values with SQL or PHP

I don't know much about SQL and thus i've made a lot of workarounds with PHP in my project, because i can't figure out a query to use and thus doing it with loops and arrays in PHP it seems always easier.

Okay, to my issue -> I have a variable i know,

seasonId
, which references to tourneys (inside a season) which reference to tourneyplayers which references to it's results and it's main player (which has all the player data, but not tourney specific information). The relation between tourneys and tourneyplayers is
1 to 1
and relation between tourneyplayers and players is
n to 1
. What i want to achieve is to display the sum of results (located in
results
table) for each player.

Now my solution would be to select all the tourneys which have
seasonId = x
and iterate over them via while loop, select their players and just calculate the player results in an array somewhere. This solution is ugly and seems even harder this time than with SQL.

My table structure:

Season | Tourney | TourneyPlayer | Results | Player
------ | ------- | ------------- | ------- | --------
SeasonId | TourneyId | tp_id | resultid | playerid
SeasonId | tourneyid | result
resultid
playerid


So let's say i have season X and it has inside tourneys X1,X2,X3, with each having only one player Y and accordingly Y1,Y2,Y3 in tourneys, then the query would return sum of 3 results (for player Y which is in 3 tourneys).

Thanks :)

Answer Source

A couple of joins should do the trick:

SELECT SUM(result)
FROM   results r
JOIN   tourneyplayer tp on tp.resultid = r.resultid
JOIN   tourney t ON t.tourneyid = tp.tourneyid
WHERE  t.seasonid = x