MYSQL Display column values only once from table

Question

I Have two tables by name invoice and invoice_product. To visiualize :

invoice table

Invoice_Number  Invoice_Date  Customer_Name  Total_Amount 

     1           2013-08-01        XYZ        10000
     2           2013-08-02        ABC         7000
     3           2013-08-03        DEF         4000

invoice_product table

Invoice_Number    Product_names     Price

       1              prd1          5000
       1              prd2          3000
       1              prd3          2000
       2              prd22         3000
       2              prd25         4000
       3              prd13         2500
       3              prd16         1500

I want to get the following output by combining both the tables.

Invoice_Number  Invoice_Date  Product_names Customer_Name  Total_Amount 

     1           2013-08-01        prd1          XYZ         10000
                                   prd2
                                   prd3

     2           2013-08-02        prd22         ABC         7000
                                   prd25

     3           2013-08-03        prd13         DEF         4000
                                   prd16

I tried the following query.

select Invoice_Number,Invoice_Date,Product,Customer_Name,Total_Amount from invoice,invoice_product where invoice.Invoice_Number=invoice_product.invoice_Number;

I am getting the following output.

Invoice_Number  Invoice_Date  Product_names Customer_Name  Total_Amount 

     1           2013-08-01        prd1          XYZ         10000
     1           2013-08-01        prd2          XYZ         10000
     1           2013-08-01        prd3          XYZ         10000

     2           2013-08-02        prd22         ABC         7000
     2           2013-08-02        prd25         ABC         7000

     3           2013-08-03        prd13         DEF         4000
     3           2013-08-03        prd16         DEF         4000

Shall I need to use anything like Having,Distinct,Group By in the query to get the desired output? I am newbie to mysql. Any help is appreciated.

you can do it in php. just do the manipulation during iteration. — John Woo
– John Woo, Commented Aug 6, 2013 at 15:12

Mike Brant · Accepted Answer · 2013-08-06 15:17:57Z

1

If you truly want only one row per invoice, but want each product listed, I would suggest using GROUP_CONCAT() function like this:

SELECT
  i.Invoice_Number AS Invoice_Number,
  i.Invoice_Date AS Invoice_Date,
  GROUP_CONCAT(ip.Product) AS products,
  i.Customer_Name AS Customer_Name,
  i.Total_Amount AS Total_Amount
FROM invoice AS i
INNER JOIN invoice_product AS ip
  ON i.Invoice_Number=ip.invoice_Number
GROUP BY i.Invoice_Number

By default GROUP_CONCAT() uses commas as separators between values so for products column you would get values like prd1,prd2,prd3. You can specify a different separator as desired.

answered Aug 6, 2013 at 15:17

Mike Brant

71.4k10 gold badges102 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MYSQL Display column values only once from table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related