CODEWITHSUNDEEP
mysqljoin
Chan Chun Weng
Chan Chun Weng

Reputation: 896

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'

Upvotes: 1

Views: 38

Answers (1)

Paul Stanley
Paul Stanley

Reputation: 4098

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

Upvotes: 1

Related Questions