One-to-Many SQL SELECT concatenated into single row

Question

I'm using Postgres and I have the following schemes.

Orders

| id |    status   |
|----|-------------|
|  1 |  delivered  |
|  2 | recollected | 

Comments

| id |   text  | user | order |
|----|---------|------|-------|
|  1 | texto 1 |  10  |   20  |
|  2 | texto 2 |  20  |   20  |

So, in this case, an order can have many comments.

I need to iterate over the orders and get something like this:

| id |    status   |    comments    |
|----|-------------|----------------|
|  1 |  delivered  | text 1, text 2 |
|  2 | recollected |                |

I tried to use LEFT JOIN but it didn't work

SELECT
    Order.id,
    Order.status,
    "Comment".text
FROM  "Order" 
LEFT JOIN "Comment" ON Order.id = "Comment"."order"

it returns this:

| id |    status   | text   |
|----|-------------|--------|
|  1 |  delivered  | text 1 |
|  1 |  delivered  | text 2 |
|  2 |  recollected|        |

Answer 1

You are almost there - you just need aggregation:

SELECT
    o.id,
    o.status,
    STRING_AGG(c.text, ',') comments
FROM  "Order" o
LEFT JOIN "Comment" c ON p.id = c."order"
GROUP BY o.id, o.status

I would strongly recommend against having a table (and/or a column) called order: because it conflicts with a language keyword. I would also recommend avoiding quoted identifiers as much as possible - they make the queries longer to write, for no benefit.

Note that you can also use a correlated subquery:

SELECT
    o.id,
    o.status,
    (SELECT STRING_AGG(c.text, ',') FROM "Comment" c WHERE c."order" = p.id) comments
FROM  "Order" o