title: SQl Query :Joining 4 Tables :One to Many
body: <p>I have 4 Tables in my SQL server DB:</p>
  
  <pre><code>OrderMaster (OrderId [primaryKey], Orderdate, TotalAmount, SupplierName)
  OrderDetails (OrderDetailId[primaryKey], OrderId, Item_Id, Quantity, ManufacturerId)
  ItemMaster (Item_Id [primaryKey], ITem Name, ITemCost)
  ManufacturerMaster (ManuId[primaryKey], ManufacturerName, HandlingFees)
  </code></pre>
  
  <p>The Relations are:</p>
  
  <ul>
  <li>OrderDetails table Order id is refering the primary key of OrderMAster table(one to many)</li>
  <li>OrderDetails table Item_Id is refering the primary key of ItemMaster</li>
  <li>ManufacturerIdtable Order id is refering the primary key of ManufacturerMaster</li>
  </ul>
  
  <p>Orderdetails table can have many records for one record in ORderMaster table.</p>
  
  <p>Now I want to have a query to List all Orders which <code>orderdate=05/01/2009</code> with the below Items as columns values:</p>
  
  <ol>
  <li>Order number</li>
  <li>Total Number of Items (number of child records in orderdetail table) for each order</li>
  <li>Total (Sum of) ITemcost for all the items in an order.</li>
  </ol>
  
  <p>How to do the Order by?</p>