Zac Zac - 7 months ago 10
SQL Question

Sum between 3 linked tables SQL Server

I am trying to pull a sum from linked table.

Order:

OrderID LocationID OrderDate
100 1 1/1/2000
200 2 1/2/2000


OrderedItems:

ID OrderID ItemID
1 100 1
2 200 2
3 200 2
4 100 3


OrderItem:

ItemID ItemName Cost
1 Mobile1 100.00
2 Mobile2 200.00
3 Mobile3 300.00


The
Order
table is effectively a group of
OrderedItems
. Each row in
OrderedItems
links back to
OrderItem
via the
ItemID
.

I am trying to add a column to the below query for order total.

Order Number Location Date Ordered Order Total
-------------------------------------------------------
100 Sydney 1/1/2000 400
200 Brisbane 1/2/2000 400


The current query I have is:

SELECT
Order.OrderID AS [Order Number],
OL.Name AS [Location],
Order.OrderDate AS [Date Ordered]
FROM
Order
LEFT JOIN
Office_Locations AS OL ON OL.id = Order.LocationID


I have tried to follow this link however I am needing to link through 3 tables for the values to add.

Any hep would be great!

Answer

You're not finding a sum from three tables. You're finding a sum from one table: the OrderItem table. The only trick is getting the JOIN and GROUP BY expressions done correctly to make that column available.

SELECT o.OrderID As [Order Number], l.Name As Location
    , o.OrderDate As [Date Ordered], SUM(i.Cost) As [Order Total]
FROM [Order] o
INNER JOIN Office_Locations l on l.id = o.LocationID
INNER JOIN OrderedItems oi on oi.OrderID = o.OrderID
INNER JOIN OrderItem i ON i.ItemID = oi.ItemID
GROUP BY o.OrderID, l.Name, o.OrderDate

SQLFIDDLE