Better way to select from nested tables in PostgreSQL

I have the following schema with millions of rows in the match table and each match have two sides (away and home). I want to create a view which shows the most significant data regarding the matches replacing the ids with names. Names can change, this is the reason of the separate tables for coaches and teams.

I came up with two approaches and I am not satisfied with either of the two.

```
CREATE VIEW "fumbbl"."matches" AS
SELECT "fmid", "time",
(SELECT "name" FROM "fumbbl"."division"
WHERE "fdid" = m."fdid") "division",
(SELECT "coachbracket" FROM "fumbbl"."side"
WHERE "fmid" = m."fmid" AND "away" IS FALSE) "hbracket",
(SELECT "name" FROM "fumbbl"."coach"
WHERE "fcid" = (SELECT "fcid" FROM "fumbbl"."side"
WHERE "fmid" = m."fmid" AND "away" IS FALSE)) "hcoach",
(SELECT "name" FROM "fumbbl"."team"
WHERE "ftid" = (SELECT "ftid" FROM "fumbbl"."side"
WHERE "fmid" = m."fmid" AND "away" IS FALSE)) "hteam",
(SELECT "kilotv" FROM "fumbbl"."side"
WHERE "fmid" = m."fmid" AND "away" IS FALSE) "hktv",
(SELECT "race" FROM "fumbbl"."team"
WHERE "ftid" = (SELECT "ftid" FROM "fumbbl"."side"
WHERE "fmid" = m."fmid" AND "away" IS FALSE)) "hrace",
(SELECT "score" FROM "fumbbl"."side"
WHERE "fmid" = m."fmid" AND "away" IS FALSE) "hscore",
(SELECT "score" FROM "fumbbl"."side"
WHERE "fmid" = m."fmid" AND "away" IS TRUE) "ascore",
(SELECT "race" FROM "fumbbl"."team"
WHERE "ftid" = (SELECT "ftid" FROM "fumbbl"."side"
WHERE "fmid" = m."fmid" AND "away" IS TRUE)) "arace",
(SELECT "kilotv" FROM "fumbbl"."side"
WHERE "fmid" = m."fmid" AND "away" IS TRUE) "aktv",
(SELECT "name" FROM "fumbbl"."team"
WHERE "ftid" = (SELECT "ftid" FROM "fumbbl"."side"
WHERE "fmid" = m."fmid" AND "away" IS TRUE)) "ateam",
(SELECT "name" FROM "fumbbl"."coach"
WHERE "fcid" = (SELECT "fcid" FROM "fumbbl"."side"
WHERE "fmid" = m."fmid" AND "away" IS

In Postgres 9.4 you can simplify with the aggregate FILTER clause:

CREATE VIEW fumbbl.matches AS
SELECT m.fmid, m.time, d.name AS division
     , min(s.coachbracket) FILTER (WHERE NOT s.away) AS hbracket
     , min(t.name)         FILTER (WHERE NOT s.away) AS hteam
     , min(c.name)         FILTER (WHERE NOT s.away) AS hcoach
     , min(s.score)        FILTER (WHERE NOT s.away) AS hscore
     , min(t.race)         FILTER (WHERE NOT s.away) AS hrace
     , min(s.kilotv)       FILTER (WHERE NOT s.away) AS hktv

     , min(s.coachbracket) FILTER (WHERE s.away) AS abracket
     , min(t.name)         FILTER (WHERE s.away) AS ateam
     , min(c.name)         FILTER (WHERE s.away) AS acoach
     , min(s.score)        FILTER (WHERE s.away) AS ascore
     , min(t.race)         FILTER (WHERE s.away) AS arace
     , min(s.kilotv)       FILTER (WHERE s.away) AS aktv
FROM   fumbbl.match    m
JOIN   fumbbl.division d USING (fdid)
JOIN   fumbbl.side     s USING (fmid)
JOIN   fumbbl.team     t USING (ftid)
JOIN   fumbbl.coach    c USING (fcid)
GROUP  BY m.fmid, d.fdid  -- PK columns
ORDER  BY m.fmid DESC;

Joins to each table only once. But it needs an aggregate step to fold home and away side into one row.

Details for the new FILTER clause:

• Return counts for multiple ranges in a single SELECT statement

StackExchange Database Administrators Q#94773, answer score: 2