SQL 2 Excel export orders help

[hominid4]

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]

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.

[hominid4]

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