CODEWITHSUNDEEP
mysqlsqljoin
Blind Fish
Blind Fish

Reputation: 998

Complex where clause with multiple joins in SQL

Let's say I have a database where I am tracking statistics for several soccer leagues. My tables and relevant columns are as follows.

leagues: id teams: id, league_id players: id, team_id goals: id, player_id, num_goals

As you can see, this is essentially a series of hasManyThrough relationships. A league has many players through teams. A team has many goals through players. Etc. Where I'm breaking down is in writing a query that sums the number of goals by league. My best effort so far is something like this:

select('id', 'name') from leagues,
join teams where ('teams.id' = 'leagues.id'),
join players where ('players.id' = 'teams.id'),
join goals where ('goals.id' = 'players.id'),
sum('goals.num_goals');

The goal of that query was to return an array of league id, league name, and the number of goals scored by players in that league.

Upvotes: 1

Views: 368

Answers (1)

Nir Levy
Nir Levy

Reputation: 12953

your joins are ok, the problem is with how you do the sum.

Sum statment should be in the select statement, and preferably come with with group by statement. you also shouldn't wrap column names with ' signs

select leagues.id, leagues.name, sum(goals.num_goals) from leagues
  join teams on teams.id = leagues.id
  join players on players.id = teams.id
  join goals on goals.id = players.id
group by leagues.id, leagues.name;

Upvotes: 1

Related Questions