|
|
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:
| docno | docdate | customername | doctotal | Items_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 |
|