CODEWITHSUNDEEP
mysqlsql
user9923440
user9923440

Reputation:

How to Join My Invoice and Invoice Product table with sql query..?

I have two table invoice and invoice product table, i want to join my two table with respect to each invoice id.

I have tried to write sql query but it is wrong.

SELECT invoice.invoice_id, invoice_product.product_name
FROM invoice, invoice_product
WHERE invoice.invoice_id = invoice_product.invoices_id

I want to show my actually result like this.


Invoice_id | Invoice_name | Product_name                | Created Date
========== |============= |=============================|==============
   1       |    Test 1    |   Product1 ,product 2       | 2019-05-02
   2       |    Test 2    |    New Product ,New Product | 2019-05-02

invoice table:

enter image description here

invoice_product table:

enter image description here

Upvotes: 1

Views: 1311

Answers (3)

Nick
Nick

Reputation: 147176

You need to use a GROUP_CONCAT to get the list of products like that, grouping by invoice number. This will work:

SELECT i.invoice_id, i.invoice_name, GROUP_CONCAT(ip.product_name) AS products, i.created_at
FROM invoice i
JOIN invoice_product ip ON i.invoice_id = ip.invoices_id
GROUP BY i.invoice_id

Output:

invoice_id  invoice_name    products                        created_at
1           Test 1          Product 1,Product 2             2019-05-02 00:00:00
2           Test 2          Product New,Product New New     2019-05-02 00:00:00

Demo on dbfiddle

Note that implicit JOIN syntax using comma has been deprecated for a while, it is better to write explicit JOINs instead.

Upvotes: 1

Arnaud Peralta
Arnaud Peralta

Reputation: 1305

Avoid jointure with your actual syntax, this is too old. I do not recommand to have non-atomic values but the GROUP_CONCAT function is doing the work for you.

SELECT invoice.invoice_id, GROUP_CONCAT(IP.product_name), I.created_at
FROM invoice AS I
JOIN invoice_product AS IP
    ON I.invoice_id = IP.invoices_id
GROUP BY IP.invoices_id

Upvotes: 1

Fahmi
Fahmi

Reputation: 37473

use group_concat()

SELECT invoice.invoice_id, group_concat(invoice_product.product_name)
FROM invoice join  invoice_product
on invoice.invoice_id = invoice_product.invoices_id
group by invoice.invoice_id

Upvotes: 0

Related Questions