Hey Everyone,
So first off this is a homework task I am working on and its just been so long since I've really had to build any deep SQL queries and feel like I am spinning my wheels. I have attached the SQL data to this thread and I am using
www.sqlfiddle.com as my testing ground. The format of teh data returned does not matter, I only need to make sure that I return all pieces of data required and no repeating data.
My task:
Provide the SQL code for a complex join query to display all of the information contained in the attached Sales Order Form.
The "Sales Order Form" is not attached but the format is as follows:
Code:
Date:
Donut Order ID:
Customer ID:
Customer: [First Name] [Last Name]
[Street Address] [Apt. #]
[City, ST ZIP Code]
[Home Phone] [Mobile Phone] [Other Phone]
Qty Donut ID Name Description Unit Price
1 1 Plain Plain Donut $1.50
5 2 Glazed Glazed Donut $1.75
12 3 Cinnamon Cinnamon Donut $1.75
3 4 Chocolate Chocolate Donut $1.75
4 5 Sprinkle Sprinkle Donut $1.75
5 6 Gluten-Free Gluten-Free Donut $2.00
Total
Special Handling Notes:
Please include plates and napkins.
So far I have the following SQL query which will obtain most of the customer information and the order total:
Code:
SELECT cFName, cLName, cAddress, cApt, cCity, cState, cZip, cHPhone, cMPhone, cOPhone, `Order`.idOrder, oDate, oTotal, oNotes
FROM Customers
RIGHT JOIN `Order`
ON `Customers`.`idCustomers` = `Order`.`idCust`;
This returns exactly what I am looking for:
Code:
cFName cLName cAddress cApt cCity cState cZip cHPhone cMPhone cOPhone idOrder oDate oTotal oNotes
Bob Smith 123 Street (null) Phoenix AZ 85123 602-123-4567 (null) (null) 1 May, 06 2014 00:00:00 58.85 Please include plates and napkins.
I am running into issues getting a SQL query that only returns one line item for each donut purchased on the order. No matter what type of query or join I create it is returning repeating rows.
I need to obtain:
dName, dDesc from the Donuts table
dQuantity, dPrice and idDonut from the OrderDetails table.
Join these together to provide a line item list like in the above. The total price for the line item does not need to be calculated in the SQL query.
Any help is greatly appreciated, remember this is a homework question and although a solution would be great I would prefer to understand where I am messing up and what the right solution is. I have ommitted my query/join attempts as they are all wrong but if you want to see what I tried just let me know.
Thanks!
