join three tables in sql server 2005

Question

I ve thus far used join with two tables but now i want to join three tables which is shown in the below fig

_{(source: microsoft.com)}

I ve tried to join two tables,

SELECT O.OrderID,O.CustID,O.OrderTotal,C.Name from Orders
as O inner join Customers as C on O.CustID=C.CustID 

how to join the third table with this.... Any suggestion...

Answer 1

You do the same, with the third table:

SELECT O.OrderID,O.CustID,O.OrderTotal,C.Name, OC.OrderAmount
FROM Orders as O 
INNER JOIN Customers as C 
  ON O.CustID=C.CustID 
INNER JOIN OrderItems as OC
  ON O.OrderID=OC.OrderID 

Answer 2

You can just add another JOIN on to the end:

inner join OrderItems as OI ON O.OrderID= OI.OrderID

Note, that the top-level order info (order ID, customer ID, order total and customer name) will be returned for EACH order item within an order. So depending on scenario, you may want to retrieve the top level data first, and then return all the order item detail separately to save returning lots of duplicate data. Depends on situation, but thought it worth a mention.