Another day, another complex query. The simple structure is 3 tables: teams, players, and stats. Each has a hasMany / BelongsTo relationship with the table before it. The teams table has a column (names) and the stats table has colums (matches) and (points). I am trying to generate a list that contains three items as follows.
Team Name | # of Players W/ Matches | # of Players w/ Matches, but w/ 0 points
Now, since both of my counts require that stats.matches be greater than 0, it makes sense to use that as a join condition rather than as part of both count conditions. So that brings me to my query
SELECT
teams.name AS name,
count(stats.matches) AS matches,
count(IF(stats.points=0, 1, 0)) AS scrubs,
FROM teams
JOIN players ON players.team_id = teams.id
LEFT JOIN stats ON stats.player_id = players.id AND stats.matches>0
GROUP BY name
ORDER BY scrubs DESC
LIMIT 8;
This is throwing a syntax error on the LEFT JOIN. Any thoughts on the error? Or, better yet, any thoughts on a more eloquent way of making this work?
via Chebli Mohamed
Aucun commentaire:
Enregistrer un commentaire