title: Complex SQL Query - Joining 5 tables with complex conditions
body: <p>I have the following tables: Reservations, Order-Lines, Order-Header, Product, Customer. Just a little explanation on each of these tables:</p>
  
  <ul>
  <li><strong>Reservations</strong> Contains "reservations" for a billing customer/product combination. </li>
  <li><strong>Order-Lines</strong> Contains line item detail for orders, including the product they ordered and the qty.</li>
  <li><strong>Order-Header</strong> Contains header info for orders including the date, customer and billing customer</li>
  <li><strong>Product</strong> Contains product detail information</li>
  <li><strong>Customer</strong> Contains Customer detail information.</li>
  </ul>
  
  <p>Below are the tables with their associated fields and sample data:</p>
  
  <pre><code>Reservation
  bill-cust-key  prod-key  qty-reserved  reserve-date
  10000          20000     10            05/30/2014
  10003          20000     5             06/20/2014
  10003          20001     15            06/20/2014
  10003          20001     5             06/25/2014
  10002          20001     5             06/21/2014
  10002          20002     20            06/21/2014
  
  Order-Item
  order-num   cust-key    prod-key    qty-ordered
  30000       10000       20000       10
  30000       10000       20001       5
  30001       10001       20001       10
  30002       10001       20001       5
  30003       10002       20003       20
  
  Order-Header
  order-num   cust-key    bill-cust-key   order-date
  30000       10000       10000           07/01/2014
  30001       10001       10003           07/03/2014
  30002       10001       10003           07/15/2014
  30003       10002       10002           07/20/2014
  
  Customer
  cust-key    cust-name
  10000       Customer A
  10001       Customer B
  10002       Customer C
  10003       Customer D
  
  Product
  prod-key    prod-name
  20000       Prod A
  20001       Prod B
  20002       Prod C
  20003       Prod D
  </code></pre>
  
  <p>I am attempting to write a query that will show me customer/product combinations that exist in both the reservation and order-item tables. A little snafu is that we have a customer and a billing customer. The reservation and order-header tables contain both the customers, but the order-item table only contains the customer. The results should display the billing customer. Additionally, there can be several reservations and order-items for the same customer/product combination, so I would like to show a total sum of the qty-reserved and the qty-ordered.</p>
  
  <p>Below is an example of my desired output:</p>
  
  <pre><code>bill-cust-key   cust-name   prod-key    prod-name   qty-ordered qty-reserved
  10000           Customer A  20000       Prod A      10          10
  10003           Customer D  20001       Prod B      15          20
  </code></pre>
  
  <p>This is the query that I have tried and doesn't seem to be working for me.</p>
  
  <pre><code>SELECT customer.cust-key, customer.cust-name, product.prod-key, prod.prod-name,
       SUM(order-item.qty-ordered), SUM(reservation.qty-reserved)
  FROM ((reservation INNER JOIN order-item on reservation.prod-key = order-item.product-key)
       INNER JOIN order-header on reservation.bill-cust-key = order-header.bill-cust-key and    
       order-item.order-num = order-header.order-num), customer, product
  WHERE customer.cust-key = reservation.bill-cust-key
  AND product.prod-key = reservation.prod-key
  GROUP BY customer.cust-key, customer.cust-name, product.prod-key, product.prod-name
  </code></pre>
  
  <p>I'm sorry for such a long post! I just wanted to make sure that I had my bases covered!</p>