Say I’ve got an orders table, an ordered_products table, and order_costs table, like so:
orders:
--------------------
orderID | orderName
1 | bob
2 | fred
--------------------
order_products
-------------------------
orderID | productID | cost
1 | 100 | 10
1 | 101 | 20
-------------------------
ordered_costs
------------------------------
orderID | costDesc | costValue
1 | shipping | 5
------------------------------
If I want to get the cost, and the product cost for a given order, What would be the best way to do it? My first thought would normally go to a simple 3 way join:
SELECT o.orderID, SUM(p.cost) AS product_cost, SUM(c.cost) AS order_cost
FROM
orders o
JOIN ordered_products p ON o.orderID=p.orderID
JOIN order_costs c ON o.orderID=c.orderID
WHERE o.orderID=1
GROUP BY o.orderID
However, that would be incorrect because it would give me:
------------------------------
orderID | product_cost| orderCost
1 | 30 | 10
------------------------------
I understand that it’s because the flat table before grouping would be:
------------------------------
orderID | product_cost| orderCost
1 | 10 | 5
1 | 20 | 5
------------------------------
What’s the best way to return the correct result here? I would usually do something like joining one table at a time, within a series of subqueries:
SELECT o.orderID, o.product_cost, SUM(c.cost) AS order_cost
FROM
(
SELECT o.orderID, SUM(p.cost) AS product_cost
FROM
orders o
JOIN ordered_products p ON o.orderID=p.orderID
WHERE o.orderID=1
GROUP BY o.orderID
) AS o
JOIN order_costs c ON o.orderID=c.orderID
GROUP BY o.orderID
But, if I were to join a large number of records, the subquery requires an un-indexed join, which I believe is inefficient and potentially slow. Another option is a correlated subquery in a similar way:
SELECT o.orderID, SUM(p.cost) AS product_cost, (SELECT SUM(c.cost) orderCost FROM order_costs c WHERE c.orderID=o.orderID) AS order_cost
FROM
orders o
JOIN ordered_products p ON o.orderID=p.orderID
WHERE o.orderID=1
GROUP BY o.orderID
But isn’t this similarly inefficient, especially if I wanted to SUM many columns in each table?
Any suggestions in this scenario? I’m probably missing something very easy.