Craig Craig - 1 month ago 8
MySQL Question

How to retrieve 'postcode' from separate table using SQL

Goal

To Pull order details from sales_flat_order table and include postcode of order from sales_flat_order_address table in Magento 1.9

My Query so far (working)

SELECT `increment_id` AS 'Order Number', SUM(ROUND(`base_grand_total`,2)) AS 'Total Value', created_at AS 'Order Date', customer_firstname AS 'First Name', customer_lastname AS 'Surname'
FROM `sales_flat_order`
WHERE `shipping_description` LIKE '%next day%'
AND `created_at` BETWEEN '2015-11-01' AND '2015-11-05'
GROUP BY `increment_id`
ORDER BY `created_at` DESC


Results so far

Order Number | Total Value | Order Date | First Name | Surname

Problem

As you can see, I'm okay with pulling the data from sales_flat_order, but struggling to pull data from sales_flat_order_address.

Solution

The following query has returned my results, but I feel it's not efficient. If there is a more efficient way, please let me know. Thanks

SELECT `increment_id` AS 'Order Number', SUM(ROUND(`base_grand_total`,2)) AS 'Total Value', created_at AS 'Order Date', customer_firstname AS 'First Name', customer_lastname AS 'Surname', `postcode` AS 'Postcode'
FROM `sales_flat_order`, `sales_flat_order_address`
WHERE `shipping_description` LIKE '%next day%'
AND sales_flat_order.billing_address_id = sales_flat_order_address.entity_id
AND `created_at` BETWEEN '2015-11-01' AND '2015-11-05'
GROUP BY `increment_id`
ORDER BY `created_at` DESC

Answer

The preferred syntax now is to use JOIN .... ON, but otherwise no difference in efficiency as far as I know.

SELECT `increment_id` AS 'Order Number', SUM(ROUND(`base_grand_total`,2)) AS 'Total Value', created_at AS 'Order Date', customer_firstname AS 'First Name', customer_lastname AS 'Surname', `postcode` AS 'Postcode'
FROM `sales_flat_order`
JOIN `sales_flat_order_address` ON sales_flat_order.billing_address_id = sales_flat_order_address.entity_id
WHERE `shipping_description` LIKE '%next day%'
AND `created_at` BETWEEN '2015-11-01' AND '2015-11-05'
GROUP BY `increment_id`
ORDER BY `created_at` DESC