SELECT op.products_quantity * SUM( products_weight )
FROM products p
JOIN orders_products op
WHERE p.products_id
IN (
SELECT products_id
FROM orders_products
WHERE orders_id = 'ORDER_ID'
)
AND op.orders_id = 'ORDER_ID'
FROM products p
JOIN orders_products op
WHERE p.products_id
IN (
SELECT products_id
FROM orders_products
WHERE orders_id = 'ORDER_ID'
)
AND op.orders_id = 'ORDER_ID'
Replace ORDER_ID with the orders_id in question.
Requires MySQL 4.1 or better for subselect support
Popularity: 9% [?]
your query does not count more than 1 items of the same products
more accurate query should be sth like this:
SELECT op.products_quantity*sum(p.products_weight)
FROM products p JOIN orders_products op
WHERE p.products_id IN (SELECT products_id FROM orders_products WHERE orders_id = ‘ORDER_ID’)
AND op.orders_id = ‘ORDER_ID’
Good catch!
I updated the original query to avoid confusion.