SQL Query to show Voucher Code Name

[peppermike]

Hello I am a bit of an SQL and Presta newbie so would love some help on this which will probably be useful to others.

 

I have set up an SQL (part my work part cobbled together from other queries I can find) to get a weekly order report. 

I can see customers, addresses, value and any discounts from this query but I cannot work out how to join the cart_rule to get the Voucher Code used.

 

This is my current query without the Voucher code field which works great.

SELECT o.`id_order` AS `id`,

    CONCAT(LEFT(c.`firstname`, 1), '. ', c.`lastname`) AS `Customer`,

    CONCAT(LEFT(ad.`firstname`, 1), '. ', ad.`lastname`) AS `Del_Name`,  

c.`email` AS `email`,

ad.`address1` AS `Address1`,

ad.`address2` AS `Address2`,

ad.`city` AS `City`,

ad.`postcode` AS `Postcode`,

    o.`payment`, CONCAT(o.`total_paid_real`, ' ', cu.`sign`) AS `Total`,

    o.`date_add` AS `Date`,

    o.`total_discounts` AS `Discount`

FROM `ps_orders` o

LEFT JOIN `ps_customer` c ON (o.`id_customer` = c.`id_customer`)

LEFT JOIN ps_address ad ON (o.id_address_delivery = ad.id_address)

LEFT JOIN `ps_currency` cu ON (o.`id_currency` = cu.`id_currency`)

where o.date_add between '2015-03-25' and '2015-04-01'

 

However when I try and add the Voucher Code from the ps_cart_rule table it only returns a blank even if a Voucher has been used.

This is my latest attempt...

SELECT o.`id_order` AS `id`,

    CONCAT(LEFT(c.`firstname`, 1), '. ', c.`lastname`) AS `Customer`,

    CONCAT(LEFT(ad.`firstname`, 1), '. ', ad.`lastname`) AS `Del_Name`,  

c.`email` AS `email`,

ad.`address1` AS `Address1`,

ad.`address2` AS `Address2`,

ad.`city` AS `City`,

ad.`postcode` AS `Postcode`,

    o.`payment`, CONCAT(o.`total_paid_real`, ' ', cu.`sign`) AS `Total`,

    o.`date_add` AS `Date`,

    o.`total_discounts` AS `Discount`,

cr. `code` AS 'CartCode'

FROM `ps_orders` o

LEFT JOIN `ps_customer` c ON (o.`id_customer` = c.`id_customer`)

LEFT JOIN ps_address ad ON (o.id_address_delivery = ad.id_address)

LEFT JOIN `ps_currency` cu ON (o.`id_currency` = cu.`id_currency`)

LEFT JOIN `ps_cart_rule` cr ON (o.`id_customer` = cr.`id_customer`)

where o.date_add between '2015-03-25' and '2015-04-01

 

I am probably missing something obvious but what appreciated a bit of guidance on what I am doing wrong.

 

Thanks

 

Mike

 

[tomerg3]

LEFT JOIN `ps_cart_rule` cr ON (o.`id_customer` = cr.`id_customer`)

Will match any code that is assigned to the customer, which is not what you want

You need to get the id_cart_rule from order_cart_rule for the current id_order, and use that to join the cart_rule table

[peppermike]

HI tomerg3

 

Thanks for that. Now I have the right table I have managed to get a query that returns the Voucher Description ('name') from the order_cart_rule table. This works for my needs but as I nice to have I would also like the query to show the actual Voucher Code from the id_cart_rule table.

 

Not sure if this is possible?

 

Here is my new query which does the job so thanks again for your help. Mike

 

SELECT o.`id_order` AS `id`,
    CONCAT(LEFT(c.`firstname`, 1), '. ', c.`lastname`) AS `Customer`,
    CONCAT(LEFT(ad.`firstname`, 1), '. ', ad.`lastname`) AS `Del_Name`,   
c.`email` AS `email`,
ad.`address1` AS `Address1`,
ad.`address2` AS `Address2`,
ad.`city` AS `City`,
ad.`postcode` AS `Postcode`,
cr.`name` AS `Code`,
    o.`payment`, CONCAT(o.`total_paid_real`, ' ', cu.`sign`) AS `Total`,
    o.`date_add` AS `Date`,
    o.`total_discounts` AS `Discount`
FROM `ps_orders` o
LEFT JOIN `ps_customer` c ON (o.`id_customer` = c.`id_customer`)
LEFT JOIN ps_address ad ON (o.id_address_delivery = ad.id_address)
LEFT JOIN `ps_order_cart_rule` cr ON (o.`id_order` = cr.`id_order`)
LEFT JOIN `ps_currency` cu ON (o.`id_currency` = cu.`id_currency`)
where o.date_add between '2014-09-26' and '2015-04-08'

[cuapaca]

Hello!
Does anybody knows how to extend this code to show the badge success in a new row?
I added

 IF(a.valid, 1, 0) badge_success to query, but returns an empty CSV
 

SELECT o.`id_order` AS `id`, c.`firstname`,  c.`lastname`,
 c.`email` AS `email`,
cr.`name` AS `Cupon`,
    o.`payment`, CONCAT(o.`total_paid_real`, ' ', cu.`sign`) AS `Total`,
    o.`date_add` AS `Date`,
    o.`total_discounts` AS `Discount`,
 IF(a.valid, 1, 0) badge_success
FROM `ps_orders` o
LEFT JOIN `ps_customer` c ON (o.`id_customer` = c.`id_customer`)
LEFT JOIN ps_address ad ON (o.id_address_delivery = ad.id_address)
LEFT JOIN `ps_order_cart_rule` cr ON (o.`id_order` = cr.`id_order`)
LEFT JOIN `ps_currency` cu ON (o.`id_currency` = cu.`id_currency`)
where o.date_add between '2017-09-01' and '2017-09-30'

Thanks in advanced!

[cuapaca]

Hey Folks!
I did it, when posting I realized that I had:

 IF(a.valid, 1, 0) badge_success

So I replaced for:

 IF(o.valid, 1, 0) badge_success

 

As I was searching in the "orders" table.

Hope will be useful for anybody