Multiple foreign keys to same table and group

Question

I need to SELECT all matches and group them by teams in specified contest.
The problem is that in matches I have 2 foreign keys to teams

host_id
guest_id

To SELECT both teams I SELECT contest and JOIN:

JOIN teams host ON matches.host_id = host.id JOIN teams guest ON matches.guest_id = guest.id

But I have problem how to group them to could SUM all statistics from matches for distinct teams. Not multiplied for both as host and guest.
Because if I would group them by both host.name and guest.name I will get SUM for Team1 like host and SUM for Team1 like guest seperatly.

First, both of these joins select different rows for any given team (since a team can only be guest or host). Second, what exactly can you not SUM() this way? Show the complete query. — marekful, Jul 21 '14 at 19:59
Does this answer your question? [Two SQL LEFT JOINS produce incorrect result](https://stackoverflow.com/questions/12464037/two-sql-left-joins-produce-incorrect-result) — philipxy, Mar 28 '23 at 08:10

score 0 · Answer 1 · answered Jul 21 '14 at 20:30

Create a subselect that gethers the aggregates you want, then join back to your main statement.

(rest of your joins)
join
(select host.id, sum(score) totalscore,avg(gametime) average_gametime,sum(whateverelse)
from host
group by host.ID) a
on host.id = a.id

Now you can call a.totalscore, a.average_gametime, or whatever else in your select clause

score 0 · Answer 2 · answered Jul 22 '14 at 00:41

You seem to want match info for a team when it was home and when it was guest:

select t.id, count(*), sum(...), ...
from teams t join matches m
where t.id = m.home or t.id=m.guest
group by (t.id)

Your information for a match other than team id may now also be separate for home and guest. Eg matches columns home_score & away_score. Then you need to use "or" to get at that information too:

select t.id,
    case when when t.id = m.home then "home" when t.id = m.guest then "guest" end case) as role,
    sum(*) as games_played,
    sum(case when t.id = m.home then m.home_score when t.id = m.guest then m.home_guest end case) as total_score,
    case when t.id = m.home then m.home_penalties when t.id = m.guest then m.home_penalties end case) as total_penalties,
    ...

This could be written with fewer case expressions but probably more slowly as:

select id, role, count(*), sum(score), sum(penalty)
from teams t
join (select home as team, "home" as role, m.home_score as score, m.home_penalty as penalty, ..., match.* from matches)
    union
    select away as team, "away" as role, m.away_score as score, m.away_penalty as penalty, ..., match.* from matches) m
on t.id = m.team
group by (t.id)

To avoid these complications, you could keep whether a team is home or away (role) plus its results per match in a table and keep only the team pairings of a match in another table then define matches with its home_/away_ columns as a view of them.

Multiple foreign keys to same table and group

2 Answers2