CODEWITHSUNDEEP
sqldatabasepostgresqlsubquery
Umesh Malhotra
Umesh Malhotra

Reputation: 1047

SQL get a column as comma-seperated values from a subquery

I have these tables in my postresql:-
1.Payment(id, number, amount)
2.Invoice(id, number, amount)
and a invoice_payment table to allocate payments amount to invoices
3.InvoicePayment(id, payment_id, invoice_id, amount)
Single payment can be allotted to many invoices. Now I want payment details, but I also want invoices.number of the invoices this payment is allotted to. I am doing something like this:-

SELECT
    payments.number,
    payments.amount,
    (SELECT invoices.number from invoices INNER JOIN invoice_payments
     ON invoices.id = invoice_payments.invoice_id
     WHERE invoice_payments.payment_id = payments.id)
from payments

But it gives me error more than one row returned by a subquery. How do I make the invoice number comma-seperated for every payments row? Sample data:- 

Payment  
id  number  amount  
1  "Pay001" 100  
2 "Pay002" 150  
3 "Pay003" 150 
Invoice  
id number amount  
1 "INV001" 100  
2 "INV002" 200  
3 "INV003"  100  
InvoicePayment  
id payment_id invoice_id amount  
1    1          1          50  
2    1          2          50  
3    2          2          150  
4    3          1           50  
5    3          3          100  

Result:-  

payment_id payment_number amount invoices  
1          "Pay001"       100    "INV001, INV002"  
2          "Pay002"       150    "INV002"  
3          "Pay003"       150    "INV001, INV002"

Upvotes: 0

Views: 48

Answers (2)

Gaj
Gaj

Reputation: 886

Try this

SELECT
    payments.number,
    payments.amount,
    inv_p.inv_no
from payments p inner join (Select invoice_payments.payment_id, string_agg(invoices.number,',') as inv_no
                            From invoices inner join invoice_payments on (invoices.id = invoice_payments.invoice_id)
                            Group by invoice_payments.payment_id) inv_p on (p.id = inv_p.payment_id)

Upvotes: 1

Amith Kumar
Amith Kumar

Reputation: 4874

You can use string_agg function in postgres to concatenate rows, separated by chosen delimiter.

SELECT
    payments.number,
    payments.amount,
    (SELECT string_agg(invoices.number,',') from invoices INNER JOIN invoice_payments
     ON invoices.id = invoice_payments.invoice_id
     WHERE invoice_payments.payment_id = payments.id)
from payments

Upvotes: 1

Related Questions