Modern SQL: With – Organize Complex Queries
What I really want from SQL is the ability to do a single select with one or more parts of each returned record being an array (defined by a one-to-many join) and others simple scalars.<p>It looks like Postgres can do it [1] with its JSON capabilities, and to be honest its being a little while since I've dug into it, so things might have changed (I'm actually not even sure I'm phrasing the question correctly), but the old way of doing a single select and then looping over it in whatever code is calling the SQL and doing N more selects, one for each row to get the sub-array is horrible.<p>[1] <a href="http://bender.io/2013/09/22/returning-hierarchical-data-in-a-single-sql-query/" rel="nofollow">http://bender.io/2013/09/22/returning-hierarchical-data-in-a...</a>