I want to list a Purchase Record for a customer by the following tables.
Table: Invoice
----------------------
invID | cusID | total
----------------------
1 | 1 | 10.5
Table: Invoice Item
--------------------
invID | prodID
---------------
1 | 1
1 | 3
Now I want to output as one row Like this: (or in PHP Fetched Row Table)
invID | cusID | prodID | total
-------------------------------
1 | 1 | 1, 3 | 10.5
What have I tried:
SELECT i.*, ii.prodID FROM invoice i, invoiceitem ii WHERE cusID = '1' AND i.invID = ii.invID
Result:
invID | cusID | prodID | total
-------------------------------
1 | 1 | 1 | 10.5
1 | 1 | 3 | 10.5
I think this will work for you. Haven't tested it so there might be a minor typo somewhere, but the concept should work.
SELECT i.invID, i.cusID, GROUP_CONCAT(ii.prodID) `prodID`, i.total
FROM invoice i
INNER JOIN invoiceitem ii ON i.invID = ii.invID
GROUP BY i.invID
You are just missing a GROUP_CONCAT over the prodIDs. You should also use ANSI INNER JOIN syntax, in preference to joining in the WHERE clause. Although MySql doesn't complain, it is also good practice to include all non-aggregated select fields in the GROUP BY, for compliance with other RDBMS's
SELECT i.invID, i.cusID, GROUP_CONCAT(ii.prodID) as prodID, i.total
FROM invoice i INNER JOIN invoiceitem ii ON i.invID = ii.invID
WHERE cusID = '1'
GROUP BY i.invID, i.cusID, i.total;