CODEWITHSUNDEEP
mysql
Aishwaryas
Aishwaryas

Reputation: 643

MYSQL for selecting values from table and show with comma separator

I have two tables:

sales Table

    ===============================
    id     cust_id     total_price
    ===============================
    1       1           1000        

    2       2           1500

sales_item Table

    ======================================================
    id     sales_id     cust_id     product     quantity
    ======================================================
    1       2           2           pen             2

    2       2           2           pencil          3

    3       1           1           book            2

    4       1           1           pencil          2

I need to query these two tables inorder to get the following result:

    =========================================
    sales_id     cust_id     product
    =========================================
    2               2           pen,pencil      

    1               1           book,pencil

Can anyone help me to query these 2 tables inorder to get the above result??

I tried using GROUP_CONCAT. Hers is what I have tried:

    SELECT s.id,s.cust_id,s.total_price,

    GROUP_CONCAT(i.prdt_name)products

    FROM sales s

    JOIN sale_items i ON s.id=i.sales_id

And the result I got is:

    ======================================================
    id     cust_id     total_price      product 
    ======================================================
    2       2           1500            pen,pencil,book

This is the not the result am expecting..

Upvotes: 0

Views: 44

Answers (2)

Aishwaryas
Aishwaryas

Reputation: 643

I got the answer by using GROUP_CONCAT itself. Here is my Query:

    SELECT s.id,s.cust_id,s.total_price,

    GROUP_CONCAT(i.prdt_name)products

    FROM sales s

    JOIN sale_items i ON s.id=i.sales_id 

    GROUP BY s.id

Upvotes: 0

Mobasher Fasihy
Mobasher Fasihy

Reputation: 1061

Try something like this, consider that this is not tested, it may help you.

select sales.id,sales.cust_id, concat(sales_item.product) 
from sales LEFT JOIN sales_item ON sales_item.sales_id = sales.id
group by sales.id

Upvotes: 1

Related Questions