SQL Complexed Join
I want to list a purchase record for a customer in 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 line. Like this: (or in a string table with PHP fetch)
invID | cusID | prodID | total
-------------------------------
1 | 1 | 1, 3 | 10.5
What I have 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
+3
source to share
2 answers
You are just missing GROUP_CONCAT through prodID
s. You should also use ANSI INNER JOIN syntax, preferring to join the WHERE clause. While MySql is not complaining, it is also recommended to include all non-aggregated select fields in the GROUP BY to match other <PC> tools in the RDBMS
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;
+2
source to share