LinuxQuestions.org
Share your knowledge at the LQ Wiki.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie
User Name
Password
Linux - Newbie This Linux forum is for members that are new to Linux.
Just starting out and have a question? If it is not in the man pages or the how-to's this is the place!

Notices


Reply
  Search this Thread
Old 05-22-2015, 05:16 PM   #1
Kustom42
Senior Member
 
Registered: Mar 2012
Distribution: Red Hat
Posts: 1,604

Rep: Reputation: 414Reputation: 414Reputation: 414Reputation: 414Reputation: 414
[Homework] Multi-table SQL Joins


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!
Attached Files
File Type: txt DonutShop.sql.txt (3.4 KB, 107 views)

Last edited by Kustom42; 05-22-2015 at 05:18 PM. Reason: Added clarification
 
Old 05-22-2015, 06:23 PM   #2
Kustom42
Senior Member
 
Registered: Mar 2012
Distribution: Red Hat
Posts: 1,604

Original Poster
Rep: Reputation: 414Reputation: 414Reputation: 414Reputation: 414Reputation: 414
Found that I could not do this all in one query as I was attempting to. Split it into two queries and got my desired result:

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`;
SELECT idDonuts, dName, dDesc, dCost
FROM Donuts
JOIN `OrderDetails`
ON `OrderDetails`.`idDonut` = `Donuts`.`idDonuts`
JOIN `Order`
ON `OrderDetails`.`idOrder` = `Order`.`idOrder`
JOIN `Customers`
ON `Customers`.`idCustomers` = `Order`.`idCust`;
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] How to describe table structure in PL/SQL developer IDE through sql command? fantasy1215 Programming 2 04-14-2013 09:20 PM
How to make an SQL table reference another table? Dornith Programming 4 10-29-2012 02:37 PM
Sql joins, confused ButterflyMelissa Programming 6 03-14-2012 09:41 AM


All times are GMT -5. The time now is 06:00 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration