4 ms·
I think PostgreSQL makes this the clearest because it supports arrays: SELECT movie.id, movie.name, array_remove(array_agg(producer.last_
by cpburns2009 10y ago
I think PostgreSQL makes this the clearest because it supports arrays:
SELECT
movie.id,
movie.name,
array_remove(array_agg(producer.last_name), NULL) AS last_names
FROM movie
LEFT JOIN producer ON movie.id = producer.movie_id
GROUP BY
movie.id,
movie.name;
- MarkusWinand 10y agoJust FYI: starting with 9.4, PostgreSQL supports the FILTER clause. This would change this: > array_remove(array_agg(producer.last_name), NULL) AS last_names to that > array_agg(producer.last_name) FILTER(WHERE producer.last_name IS NOT NULL) AS last_names I wrote about FILTER here: http://modern-sql.com/feature/filter http://modern-sql.com/feature/filter And how to use ARRAYs rather than concatenated strings here: http://modern-sql.com/feature/listagg#alternative-array http://modern-sql.com/feature/listagg#alternative-array
- cpburns2009 10y agoI wasn't aware of that feature. Now I really need to push for upgrading PostgreSQL to a newer version.