PHP/MySQL ->
MySQL string functions. Part 2

In the following query we use GROUP_CONCAT() function to concatenate items from one invoice into one line under the title "Items_List". Without doing this, query join would show us multiple lines per one invoice:

SELECT a.docno, a.docdate, a.customername, a.doctotal,
GROUP_CONCAT(itemcode) AS Items_List
FROM Invoice AS a INNER JOIN InvLines AS b
ON a.dockey = b.dockey
GROUP BY a.docno, a.docdate, a.customername, a.doctotal

The results will be as following:
docnodocdatecustomernamedoctotalItems_List
5 1999-03-12 00:00:00 Erika Nass 250 77003,77003
6 1999-04-02 00:00:00 John Depp 1750 5003298,77003
7 1999-05-17 00:00:00 Barbara Spears 6900 77112
11 1999-10-08 00:00:00 Maddalena Corvaglia 3687.5 5004423,77108,5003258


sqlexamples.info