Re: [GENERAL] sub-select with multiple records, columns

2017-06-19 Thread Israel Brewster
On Jun 19, 2017, at 12:29 PM, Thomas Kellerer wrote: > > Israel Brewster schrieb am 19.06.2017 um 22:17: >> SELECT >> ... >> (SELECT >> array_agg(to_json(row(notedate,username,note))) >> FROM sabrenotes >> INNER JOIN users ON author=users.id >> WHERE ticket=sabretickets.id ) notes >> FROM tickets

Re: [GENERAL] sub-select with multiple records, columns

2017-06-19 Thread David G. Johnston
On Mon, Jun 19, 2017 at 1:32 PM, David G. Johnston wrote: > On Mon, Jun 19, 2017 at 1:29 PM, Thomas Kellerer wrote: >> >> Israel Brewster schrieb am 19.06.2017 um 22:17: >>> >>> SELECT >>> ... >>> (SELECT >>> array_agg(to_json(row(notedate,username,note))) >>> FROM sabrenotes >>> INNER JOIN users

Re: [GENERAL] sub-select with multiple records, columns

2017-06-19 Thread Thomas Kellerer
Israel Brewster schrieb am 19.06.2017 um 22:17: SELECT ... (SELECT array_agg(to_json(row(notedate,username,note))) FROM sabrenotes INNER JOIN users ON author=users.id WHERE ticket=sabretickets.id ) notes FROM tickets WHERE ... The only problem with this query is that the notes aren't sorted. Of

[GENERAL] sub-select with multiple records, columns

2017-06-19 Thread Israel Brewster
I have two tables, a ticket table and a notes table, set up where each ticket can have multiple notes. I'm trying to come up with a query that returns the ticket fields as well as a field that is an array type field with the values being json-encoded note records. I've come up with the following su