Aggragate inner join column with prepended value from third column

Question

I have three tables. Leads, Notes and Users.

leads
- id
- name

notes
- id
- lead_id
- content
- user_id

users
- id
- name

I would like to construct a query to return new table with two columns

Lead name | Notes
John      | Mary - Note 1 ### John - Note 2

First column is straightforward. Every lead has a name. Second column however is a tricky one. I want to aggregate all notes to single column with addition to prepending note's author's name.

I wrote a query with second column that has aggregated notes.

 SELECT leads.name AS name,
        string_agg(notes.content, ' ### ') AS leads_notes,
 FROM leads
 INNER JOIN notes ON notes.lead_id = leads.id
 GROUP BY leads.id

But note's author name (users.name) I do not how to query.

score 1 · Accepted Answer · edited May 23 '17 at 12:27

Your quoting is misleading. Single quotes are not for identifiers.
Also your joins are twisted. You can't join users to leads directly. leads has no user_id ...

SELECT ls.name
      , string_agg(concat_ws(' - ', u.name, n.content)
                  , ' ### ') AS leads_notes
FROM   leads l
JOIN   notes n ON n.lead_id = l.id
JOIN   users u ON u.id = n.user_id
GROUP  BY l.id;

Fixed the basic structure.

Concatenate name and content with concat_ws() (multiple values from the same row) before you aggregate with string_agg() (multiple values across rows).

If users.name and notes.content are defined NOT NULL, you can just concatenate instead:

u.name || ' - ' || n.content

Details:

Combine two columns and add into one new column

You also may want to order the elements per lead in a non-arbitrary fashion:

string_agg( ... ORDER BY u.name, u.id)

Aggragate inner join column with prepended value from third column

1 Answers1