How to convert this SQL so that it uses less subqueries?

Question

SELECT s.id, s.show_start, s.show_end, s.bekeken, s.website, s.afbeelding,
         (SELECT titel FROM serie_details WHERE taalcode = 'oo' AND serie_id = s.id) AS titel,
         (SELECT beschrijving FROM serie_details WHERE taalcode = 'oo' AND serie_id = s.id) AS beschrijving,
         ARRAY_AGG(d.titel) AS alt_titels,
         (SELECT ARRAY_AGG(num[1]) FROM afleveringen WHERE serie_id = s.id) AS afleveringen,
         (SELECT COUNT(id) FROM afleveringen WHERE serie_id = s.id) AS totaal
FROM series AS s
LEFT JOIN serie_details AS d ON d.serie_id = s.id
WHERE s.id = 6
GROUP BY s.id, s.show_start, s.show_end, s.bekeken, s.website, s.afbeelding

series is the table with the series, serie_details contains alternative names for series and afleveringen contains episode numbers and titles for those episodes.

I've tried using multiple JOINs but then I get duplicated results, because there are multiple rows in the serie_details table. The query was:

SELECT s.id, s.show_start, s.show_end, s.bekeken, s.website, s.afbeelding, COUNT(a.id) AS totaal,
         (select titel from serie_details where taalcode = 'oo' and serie_id = s.id) AS titel,
         (select beschrijving from serie_details where taalcode = 'oo' and serie_id = s.id) AS beschrijving,
         ARRAY_AGG(a.num[1]) AS afleveringen,
         ARRAY_AGG(d.titel) AS alt_titels
FROM series AS s
LEFT JOIN afleveringen AS a ON a.serie_id = s.id
LEFT JOIN serie_details AS d ON d.serie_id = s.id
WHERE s.id = 6
GROUP BY s.id, s.show_start, s.show_end, s.bekeken, s.website, s.afbeelding

Answer 1

Your best bet is to leave it using the subqueries. Not sure about postgressql, but ms sql optimizes those just fine.

Answer 2

Use:

   SELECT s.id, s.show_start, s.show_end, s.bekeken, s.website, s.afbeelding,
          x.titel,
          x.beschrijving,
          ARRAY_AGG(d.titel) AS alt_titels,
          y.afleveringen,
          y.totaal
     FROM SERIES s
LEFT JOIN serie_details AS d ON d.serie_id = s.id
LEFT JOIN (SELECT sd.serie_id,
                  sd.titel,
                  sd.beschrijving
             FROM SERIE_DETAILS sd
            WHERE sd.taalcode = 'oo') x ON x.serie_id = s.id
LEFT JOIN (SELECT a.serie_id,
                  ARRAY_AGG(num[1]) AS afleveringen,
                  COUNT(id) AS totaal
             FROM afleveringen a
         GROUP BY a.serie_id) y ON y.serie_id = s.id
    WHERE s.id = 6
 GROUP BY s.id, s.show_start, s.show_end, s.bekeken, s.website, s.afbeelding, x.titel, x.beschrijving, y.afleveringen, y.totaal

You realize you were missing GROUP BY clauses?