I have tables :
Project table
id name
-------
1 A
2 B
Assignment table
id name project_id
-------------------
1 A1 1
2 A2 1
3 A3 2
I wish to write a query that returns each project with the name of the assignments created from it, like :
project_id assignments
-----------------------
1 A1,A2
2 A3
Is there any way to achieve that ?
You can join the tables and use array_agg to combine the values separated by a comma
SELECT a.id, array_agg(b.name) assignments
FROM Project a
INNER JOIN assignment b
ON a.id = b.project_ID
GROUP BY a.id
or by using STRING_AGG
SELECT a.id, STRING_AGG(b.name, ', ' ORDER BY b.name) assignments
FROM Project a
INNER JOIN assignment b
ON a.id = b.project_ID
GROUP BY a.id
