Variable scope in SQL `order`.id unknown column

Question

In the following query, the error Unknown column 'order.id' in 'on clause' is being thrown by my order.id reference in the INNER JOIN. The id column does indeed exist.

Any ideas why it's not accessible via the sub query?

SELECT
    SUM(price+shipping_price) AS total_sales,
    COUNT(id) AS total_orders,
    AVG(price+shipping_price) AS order_total_average,
    (SELECT
            SUM(quantity)
        FROM `order_product`
        WHERE `order`.id = order_product.order_id
    ) as total_units,
    SUM(price+shipping_price)/7 as daily_average,
    (SELECT
            SUM(order_product.price * order_return_product.quantity)
        FROM order_return_product
        INNER JOIN order_product ON (
            order_product.order_id = `order`.id AND
            order_product.product_id = order_return_product.product_id AND
            order_product.vehicle_id = order_return_product.vehicle_id
        )
        WHERE return_id IN (
            SELECT
                id
            FROM order_return
            WHERE status_id != 3 AND
            order_return.order_id = `order`.id
        )
    ) as total_returns
FROM `order`
WHERE created >= 1278388801 AND
created <= 1279079999 AND
fraud = 0 AND
type_id = 4

I get no errors when I comment out order.id within the INNER JOIN

Answer 1

I think problem is because order is keyword for mysql so use

`order`.`id `

OR

   o.id
   |
   |
   |
  FROM `order` o

Answer 2

The order.id is out of scope in this query - you're only dealing with the order_return_product and order_product tables in the subquery.

Answer 3

try this:

SELECT
    SUM(order_product.price * order_return_product.quantity)
FROM order_return_product
INNER JOIN order_product ON (
    order_product.product_id = order_return_product.product_id AND
    order_product.vehicle_id = order_return_product.vehicle_id
)
WHERE return_id IN (
    SELECT
        id
    FROM order_return
    WHERE status_id != 3 AND
    order_return.order_id = `order`.id
) 
AND order_product.order_id = `order`.id 

... for the total_returns subquery