Chan Chun Weng Chan Chun Weng - 6 months ago 32
MySQL Question

2 table join and 1 group concat

I have tables as below

sales

salesid |custid |date_created |total_price
S0001 C0001 2015-12-31 20.00
S0002 C0002 2016-01-01 10.00
S0003 C0003 2016-01-02 30.00
S0004 C0001 2016-01-05 50.00


salesdetails

salesdetailsid |salesid |product_name |quantity
D0001 S0001 pencil 3
D0002 S0001 book 2
D0003 S0001 ruler 1
D0004 S0002 Hard Cover File Folder 1
D0005 S0003 A4 Paper 1
D0006 S0003 Notebook 1
D0006 S0004 Mouse 1


My expected output should be:

if custid is C0001, output:

salesid | Purchase Date | Items
S0001 2015-12-31 pencil x 3,book x 2, ruler x 1
S0004 2016-01-05 Mouse x 1


if custid is C0002, output:

salesid | Purchase Date | Items
S0002 2016-01-01 Hard Cover File Folder x 1


if custid is C0003, output:

salesid | Purchase Date | Items
S0003 2016-01-02 A4 Paper x 1, Notebook x 1


This is what I have so far:

SELECT s.salesid AS "id",
s.time_created AS "Purchase Date",
s.total_price AS "Amount",
Group_concat(Concat(prodname, ' x ', quantity) SEPARATOR ', ') AS "Items"
FROM sales s
JOIN salesdetails d
ON s.salesid = d.salesid
WHERE s.custid = 'C0001'

Answer

You are missing your GROUP BY clause, which in effect makes your group_concat work, see if this works:

SELECT s.salesid          AS "id", 
       s.time_created     AS "Purchase Date",   
       s.total_price      AS "Amount", 
       Group_concat(Concat(prodname, ' x ', quantity) SEPARATOR ', ') AS "Items" 
FROM   sales s 
JOIN   salesdetails d 
ON     s.salesid = d.salesid 
WHERE  s.custid = 'C0001' 
GROUP BY s.salesid