dimanche 7 février 2016

LEFT JOIN with extra condition

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