SQL: Add all data from related table to a column

Question

i have these two simple tables:

Invoices:
ID       Date      ...
1     1/1/2011
2     1/2/2011

Invoice_Products:
invoiceId     productId     Quantity
1                      101           1000
1                      200               50
1                      310             125
2                      101           2000
2                      222           1000

I want a select query that will have the following result:

invoiceId      date        sumProducts
1              1/1/2011     101,200,310
2              1/2/2011            101,222

my problem is how to add the result of the select query to invoice_Products table to a single column comma delimeted.

can anyone help me?

thanks in advance...

Answer 1

You're looking for the GROUP_CONCAT() function. Something like this:

SELECT  Invoice_Products.invoiceId,
        Invoices.Date,
        GROUP_CONCAT(Invoice_Products.productId) AS productIds
  FROM Invoice_Products
  JOIN Invoices ON Invoice_Products.invoiceId = Invoices.ID
 GROUP BY invoiceId
;

See the result here.

Answer 2

TRY this.

SELECT invoiceID,DATE_FORMAT(now(),'%m/%d/%Y') as `DATE`,GROUP_CONCAT(PRODUCTID) as `sumProducts`
  FROM invoice_products
 GROUP BY invoiceID;