Author Topic: SQL 2 Excel export orders help  (Read 1276 times)


  • Beginner
  • *
  • Posts: 28
SQL 2 Excel export orders help
« on: February 03, 2013, 01:01:04 am »
Hello. I've been using SQL 2 Excel to export our orders which works fine but we're needing to show the category the product was purchased from as well.  In our SQL 2 Excel worksheet we have:
Code: [Select]
SELECT FROM_UNIXTIME(cdate) AS 'Order Date', order_id as 'Invoice #', order_item_sku as 'SKU#', order_item_name as 'Product', product_attribute as 'Attribute', product_quantity as 'Quantity'
FROM jos_vm_order_item

Would anyone know how to pull the category the product was ordered from as well?  Sorry for asking here, Joomla R Us's website has been down for a while.


Joseph Kwan

  • Advanced
  • Full Member
  • *****
  • Posts: 2344
Re: SQL 2 Excel export orders help
« Reply #1 on: February 06, 2013, 01:33:06 am »
category is not saved in order item. You may use a series of JOIN tables to get the category name if the product belongs only to one category.
Joomla/VM Upgrade Services. Problems with your migration? We can help.
Custom extensions to VM. Performance Tuning. Template modifications and advices.
Pay service to make VM work according to your needs. Your Joomla/VM solutions are just a PM away.


  • Beginner
  • *
  • Posts: 28
Re: SQL 2 Excel export orders help
« Reply #2 on: February 08, 2013, 18:28:57 pm »
Thanks Joseph, I'll dig more in to it.  Searching through my back-ups I found the below sample code I had pulled from their site a while back before they went down.  It creates JOINS as you mentioned.  I'll use that as an example and see if I can modify it to what I need.  I'll post back if I figure it out.

Code: [Select]
SELECT a.order_id as `Order Number`, FROM_UNIXTIME(a.cdate) AS `Order Date`, FROM_UNIXTIME(a.mdate) AS `Last Modified`, b.order_status_name AS `Order Status`,
       a.order_total as `Total`, a.order_subtotal as `Subtotal`, a.order_tax as `Tax`,
       a.coupon_discount as `Coupon Discount`, a.coupon_code as `Coupon Code`, a.order_discount as `Discount`,
       c.user_email as `Email`, c.first_name as `First Name`, c.middle_name AS `Middle Name`, c.last_name as `Last Name`,
       c.address_1 AS `Address 1`, c.address_2 AS `Address 2`, AS `City`, AS `Zip Code`,
       c.state AS `State`, c.phone_1 AS `Phone`,
       a.customer_note as `Customer Note`
FROM jos_vm_orders a,
     jos_vm_order_item b,
     jos_vm_order_user_info c,
     ( SELECT sh.*
       FROM jos_vm_order_user_info sh
       WHERE order_info_id = ( SELECT MAX(order_info_id)
                               FROM jos_vm_order_user_info shmax
                               WHERE shmax.order_id = sh.order_id )
     ) AS ship
WHERE a.order_status = b.order_status_code
      AND c.address_type = 'BT'
      AND a.order_id=c.order_id
      AND ship.order_id = c.order_id
ORDER BY a.order_id DESC

Thanks again!,