SQL Concat Subquery

Question

I would like the "user_names" to be a list of users, but I can't get multiple subquery items to be assigned to "user_names". It work's if the subquery only returns 1 item, but doesn't work if it returns multiple.

Bed Table
[id]
[1]
[2]
[3]

Assignment Table:
[id, bed_id, user_id]
[1, 1, 1]
[2, 1, 2]

Users Table:
[id, 'user_name']
[1, 'John Smith']
[2, 'Jane Doe']

    sql = "SELECT
        b.id,
        (
            SELECT
                u.user_name
            FROM
                assignments AS a
            INNER JOIN
                users as u
            ON
                a.user_id = u.id
            WHERE a.bed_id = b.id
        ) AS user_names
        FROM beds AS b"

The desired results would be:
[1, 'John Smith, Jane Doe']
[2, '']
[3, '']

I tried hardcoding the bed id and running this segment to get a list of names. It didn't work:

    sql = """
        (
        SELECT
            array_agg(user_name)
        FROM
            roomchoice_assignment AS a
        INNER JOIN
            roomchoice_customuser as u
        ON
            a.user_id = u.id
        WHERE
            a.bed_id = 1
        GROUP BY user_name
        )"""

It returned the following:

[
    [
        [
            "John Smith"
        ]
    ], 
    [
        [
            "Jane Doe"
        ]
    ]
]

I was hoping for this:

['John Smith, Jane Doe']

Which database are you using? The method used for concatenating values are different. There will most likely be a duplicate question that addresses your question. — jpw, Aug 10 '15 at 23:44
Please edit your question with sample data and desired results. — Gordon Linoff, Aug 10 '15 at 23:47
I am trying to get a list of assignments and a list of users assigned to each, so I think this is the best way to write the query. The database is Postgres. — Travis Hoki, Aug 10 '15 at 23:49
But what about the array-based `group_concat` alternatives? Show us how you tried them and what happened and you can get your question re-opened. — mu is too short, Aug 11 '15 at 00:18
besides the group_concat, there was another major problem, could have been solved by left outer joins without any sub-selects because sub-selects in the select clause have to be single-valued unlike those in the from clause. — maraca, Aug 11 '15 at 00:20
@maraca I just reopened it to account for the updates if you want to take a crack at it. — mu is too short, Aug 11 '15 at 01:18

jpw · Accepted Answer · 2015-08-11T01:29:01.910

One issue with the query you have is that you're grouping by the column you're applying array_agg on. If you remove the group by you would get "{"John Smith","Jane Doe"}", but you would still be missing the bed id column, and if you want a list of all beds even if there are no assignments you should use left joins instead of subqueries (which also should be better for performance and readability).

You could use string_agg as indicated by the duplicate question.

This query:

SELECT b.id, string_agg(u.user_name, ', ') users
FROM beds AS b
LEFT JOIN assignment AS a ON a.bed_id = b.id
LEFT JOIN users as u ON a.user_id = u.id
GROUP by b.id;

Would give you a result like:

1;"John Smith, Jane Doe"
2;""
3;""

That's exactly what I needed. Thanks. – Travis Hoki Aug 12 '15 at 23:44 — Travis Hoki, Aug 12 '15 at 23:44

SQL Concat Subquery

1 Answers1