Menu
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 ?
851
SUBSTRING() function The PostgreSQL substring function is used to extract a string containing a specific number of characters from a particular position of a given string. The main string from where the character to be extracted. Optional. The position of the string from where the extracting will be starting.
A dollar sign ($) followed by digits is used to represent a positional parameter in the body of a function definition or a prepared statement. In other contexts the dollar sign may be part of an identifier or a dollar-quoted string constant.
The PostgreSQL server is process-based (not threaded). Each database session connects to a single PostgreSQL operating system (OS) process. Multiple sessions are automatically spread across all available CPUs by the OS. The OS also uses CPUs to handle disk I/O and run other non-database tasks.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
