Get info from two tables

Question

Hello i work on one project using JS, PHP & Mysql. I have 2 tables which are :

invoice (inv_id,date,total,paid,due)

invoice_infos (id, product_name,product_price,inv_id)

invoice_infos contains informations about all products of a invoice.

The column date of table invoice contains the date of the invoice, the column product_name of invoice_details contains name of product.

I want do a query which allow me view product selled in defined date.

Answer 1

You seem to be looking for a simple JOIN between both tables, using field inv_id. The FROM clause of your queries should look like :

FROM invoices iv
INNER JOIN invoice_infos if ON if.inv_id = id.inv_id

Starting from there, say you want to pull out all available information in invoice_infos and invoices for a specific date of sale :

SELECT iv.*, if.*
FROM invoices iv
INNER JOIN invoice_infos if ON if.inv_id = id.inv_id
WHERE iv.date = ?

Another typical use case is to aggregate date. For example let's compute the total price for each product sold on a given date :

SELECT if.product_name, SUM(if.product_price) sum_product_price
FROM invoices iv
INNER JOIN invoice_infos if ON if.inv_id = id.inv_id
WHERE iv.date = ?
GROUP BY if.product_name