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'
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:
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']
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;""