imageknitting

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

hominid4

  • Beginner
  • *
  • Posts: 28
SQL 2 Excel export orders help
« on: February 02, 2013, 18:01:04 pm »
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.

Thanks!

Joseph Kwan

  • Advanced
  • Hero Member
  • *****
  • Posts: 2344
Re: SQL 2 Excel export orders help
« Reply #1 on: February 05, 2013, 18:33:06 pm »
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.

hominid4

  • Beginner
  • *
  • Posts: 28
Re: SQL 2 Excel export orders help
« Reply #2 on: February 08, 2013, 11:28:57 am »
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`, c.city AS `City`, c.zip 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!,
Wesley

VirtueMart Forum

Re: SQL 2 Excel export orders help
« Reply #2 on: February 08, 2013, 11:28:57 am »