SELECT `order_number`,
`#__virtuemart_orders`.`created_on`,
COALESCE(`user_info2`.`title`, `user_info1`.`title`) AS `title`,
COALESCE(`user_info2`.`first_name`, `user_info1`.`first_name`) AS `first_name`,
COALESCE(`user_info2`.`last_name`, `user_info1`.`last_name`) AS `last_name`,
COALESCE(`user_info2`.`zip`, `user_info1`.`zip`) AS `zip`,
`user_info1`.`email`,
COALESCE(`user_info2`.`phone_1`, `user_info1`.`phone_1`) AS `phone_1`,
COALESCE(`user_info2`.`phone_2`, `user_info1`.`phone_2`) AS `phone_2`,
`order_item_sku`,
`order_item_name`,
`product_attribute`,
`product_quantity`,
`#__virtuemart_orders`.`virtuemart_order_id`,
`user_info1`.`customer_note`,
`#__virtuemart_shipmentmethods`.`shipment_element`,
COALESCE(`user_info2`.`address_1`, `user_info1`.`address_1`) AS `address_1`,
COALESCE(`user_info2`.`address_2`, `user_info1`.`address_2`) AS `address_2`,
COALESCE(`user_info2`.`address_type`, `user_info1`.`address_type`) AS `address_type`,
COALESCE(`user_info2`.`address_type_name`, `user_info1`.`address_type_name`) AS `address_type_name`
FROM `#__virtuemart_orders`
LEFT JOIN `#__virtuemart_order_items` ON `#__virtuemart_orders`.`virtuemart_order_id` = `#__virtuemart_order_items`.`virtuemart_order_id`
LEFT JOIN `#__virtuemart_order_userinfos` AS `user_info1` ON `#__virtuemart_orders`.`virtuemart_order_id` = `user_info1`.`virtuemart_order_id`
LEFT JOIN `#__virtuemart_order_userinfos` AS `user_info2` ON `#__virtuemart_orders`.`virtuemart_order_id` = `user_info2`.`virtuemart_order_id` AND `user_info2`.`address_type` = 'ST'
LEFT JOIN `#__virtuemart_orderstates` ON `#__virtuemart_orders`.`order_status` = `#__virtuemart_orderstates`.`order_status_code`
LEFT JOIN `#__virtuemart_product_manufacturers` ON `#__virtuemart_order_items`.`virtuemart_product_id` = `#__virtuemart_product_manufacturers`.`virtuemart_product_id`
LEFT JOIN `#__virtuemart_manufacturers` ON `#__virtuemart_product_manufacturers`.`virtuemart_manufacturer_id` = `#__virtuemart_manufacturers`.`virtuemart_manufacturer_id`
LEFT JOIN `#__users` ON `#__users`.`id` = `user_info1`.`virtuemart_user_id`
LEFT JOIN `#__virtuemart_countries` ON `#__virtuemart_countries`.`virtuemart_country_id` = `user_info1`.`virtuemart_country_id`
LEFT JOIN `#__virtuemart_invoices` ON `#__virtuemart_orders`.`virtuemart_order_id` = `#__virtuemart_invoices`.`virtuemart_order_id`
LEFT JOIN `#__virtuemart_paymentmethods_en_gb` ON `#__virtuemart_orders`.`virtuemart_paymentmethod_id` = `#__virtuemart_paymentmethods_en_gb`.`virtuemart_paymentmethod_id`
LEFT JOIN `#__virtuemart_shipmentmethods` ON `#__virtuemart_orders`.`virtuemart_shipmentmethod_id` = `#__virtuemart_shipmentmethods`.`virtuemart_shipmentmethod_id`
LEFT JOIN `#__virtuemart_products` ON `#__virtuemart_products`.`product_sku` = `#__virtuemart_order_items`.`order_item_sku`
WHERE DATE(`#__virtuemart_orders`.`created_on`) = DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND `#__virtuemart_orders`.`order_status` IN ('C') AND `user_info1`.`address_type` = 'BT' AND `#__virtuemart_orders`.`virtuemart_shipmentmethod_id` IN ('26','27','28')
GROUP BY `order_number`,`order_item_sku`,`product_attribute`
ORDER BY `order_number`,`#__virtuemart_orders`.`created_on`,`order_item_sku`