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'

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

Hello. Can anyone help. I am trying to create a relatively simple report I can export but I am haivng trouble locating and joining the fields in the tables. Rather than my head exploding, I wonder if someone could help me with the SQL code which details Order ID, Name, Product (s), Total, Payment Type, Delivery Address and Country, Essentailly I would like the order report from the main dashboard but with address fields including country and the product details added on. Something which I could post directly in SQL manager would make me very Thanks for your help