Sql table - relation between discount and invoice.

[JolaMat]

Hi All,

 

I have sql query which display data from invoice from 2 tables: ps_order_detail and ps_order_invoice:

 

SELECT  p.id_order_invoice,  o.id_order_invoice, o.product_quantity, o.product_reference, o.product_name,  o.product_price, o.total_price_tax_excl, p.date_add,  p.total_discount_tax_excl, p.total_paid_tax_excl 

FROM ps_order_detail as o, ps_order_invoice as p 

WHERE p.id_order_invoice=o.id_order_invoice and p.date_add >= '2016-08-01' and p.date_add < '2016-08-31' ;

 

 

I would like to display discount in line (row) with the product that has a 100% discount (it is free product) not for each product from this same invoice.

I have question according this - In which sql table I can search relation between discount and invoice?

 

Now result of query is for example for invoice 183:

 

 

I would like to:

 

Thank you for your answer,

 

 

 

Edited August 29, 2016 by JolaMat (see edit history)

[tuk66]

See the ps_order_detail table for free products. Some product price fields have zero value so you can filter out them. There is no "100% discount" table.

[JolaMat]

Thanks for your answer. 

 

I could not find data about free product (100% discount) in ps_order_detail in my Presta Shop version PrestaShop™ 1.6.0.9

But I found data about free product in ps_order_cart_rule and ps_cart_rule table.

 

SELECT  ps_cart_rule.id_cart_rule, ps_order_cart_rule.id_cart_rule, ps_cart_rule.gift_product, ps_order_cart_rule.id_order, ps_order_cart_rule.id_order_invoice  

FROM ps_order_cart_rule, ps_cart_rule

WHERE ps_order_cart_rule.id_cart_rule=ps_cart_rule.id_cart_rule

ORDER BY ps_order_cart_rule.id_order_invoice ASC;

 

I select all free given product by connection table ps_order_cart_rule and ps_cart_rule.

 

I still do not know how to put data about free given product into list of sold products from above post.