F
FancyPanda
Guest
I have the following tables: Reservations, Order-Lines, Order-Header, Product, Customer. Just a little explanation on each of these tables:
Below are the tables with their associated fields and sample data:
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
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.
Below is an example of my desired output:
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
This is the query that I have tried and doesn't seem to be working for me.
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
I'm sorry for such a long post! I just wanted to make sure that I had my bases covered!
Continue reading...
- Reservations Contains "reservations" for a billing customer/product combination.
- Order-Lines Contains line item detail for orders, including the product they ordered and the qty.
- Order-Header Contains header info for orders including the date, customer and billing customer
- Product Contains product detail information
- Customer Contains Customer detail information.
Below are the tables with their associated fields and sample data:
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
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.
Below is an example of my desired output:
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
This is the query that I have tried and doesn't seem to be working for me.
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
I'm sorry for such a long post! I just wanted to make sure that I had my bases covered!
Continue reading...