Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2005 > SQL Server 2005
|
SQL Server 2005 General discussion of SQL Server *2005* version only.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2005 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old May 11th, 2011, 07:53 AM
Authorized User
 
Join Date: May 2011
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Question Joining 3 tables in a query

i am querying these tables
StockCart: StockID (pk)
Date
Pending
Direct
IsShop
StockLindItem:StockCartID (fk)
LevelID (fk)
Quantity
Leveel: LevelID (pk)
CostPrice

With the following sql Select statement

Select StockCart.StockCartID, StockCart.Date, StockCart.Pendind, StockCart.Direct, StockCart.IsShop, Sum[StockLineItem.Quantity * Level.CostPrice]
From StockCart InnerJoin StockLineItem
ON StockCart.StockID=StockLineItem.StockID
InnerJoin Level
ON StockLineItem.LevelID=Level.LevelID

But an exception is been thrown that 'incorrect synthax near StockLineItem'.
Someone help me out.
 
Old May 11th, 2011, 09:47 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

I hope that this is just a mistake, but the table name is StockLineItem or StockLindItem
__________________
HTH

Gonzalo


================================================== =========
Read this if you want to know how to get a correct reply for your question.
(Took that from Doug signature and he Took that from Peter profile)
================================================== =========
My programs achieved a new certification :
WORKS ON MY MACHINE
================================================== =========
I know that CVS was evil, and now i got the
proof.
================================================== =========
 
Old May 11th, 2011, 10:14 AM
Authorized User
 
Join Date: May 2011
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Lightbulb joining 3 tables in a query

The table name is StockLineItem
 
Old May 11th, 2011, 10:46 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

Don't you need a group by to use a sum?
__________________
HTH

Gonzalo


================================================== =========
Read this if you want to know how to get a correct reply for your question.
(Took that from Doug signature and he Took that from Peter profile)
================================================== =========
My programs achieved a new certification :
WORKS ON MY MACHINE
================================================== =========
I know that CVS was evil, and now i got the
proof.
================================================== =========
 
Old May 11th, 2011, 11:29 AM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

Did you try INNER JOIN versus InnerJoin?

Imar
__________________
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Follow me on Twitter

Author of Beginning ASP.NET 4.5 : in C# and VB, Beginning ASP.NET Web Pages with WebMatrix
and Beginning ASP.NET 4 : in C# and VB.
Did this post help you? Click the button below this post to show your appreciation!
 
Old May 11th, 2011, 12:42 PM
Authorized User
 
Join Date: May 2011
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Lightbulb joining 3 tables in a query

Yes I used InnerJoin between StockCart and StockLineItem and also between StockLineItem and Level.
 
Old May 11th, 2011, 12:55 PM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

Not sure what you mean. What exactly did you try? And did you read my post?

Imar
__________________
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Follow me on Twitter

Author of Beginning ASP.NET 4.5 : in C# and VB, Beginning ASP.NET Web Pages with WebMatrix
and Beginning ASP.NET 4 : in C# and VB.
Did this post help you? Click the button below this post to show your appreciation!
The Following User Says Thank You to Imar For This Useful Post:
bharathkumarbs.sidd (October 25th, 2011)
 
Old May 12th, 2011, 08:15 AM
Authorized User
 
Join Date: May 2011
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Lightbulb joining 3 tables in a query

Hi
This is actually what I want to do. I have Y tables; StockCart, StockLineItem and Level. The StockCart table contains data about a Cart of items that was bought. The StockLineItem contains data about each of the items in the Cart that was purchase. The Level contains data about the product that is being bought. I want to query the table so that I can get the total cost of all the items contained in a particular Cart against them.

StockCart:
StockCartID (int) this is the primary key
Date (Date/Time)
Pending (bit)
Direct (bit)
IsShop (bit)

StockLineItem:
StockCartID (int) this is a foreign key
LevelID (int) this is a foreign key
QuantitySold (int)

Level:
LevelID (int) this is a primary key
UnitPrice (money)
Description (nvarchar)
Location (nvarchar)

Please help me with code to do this
 
Old May 12th, 2011, 08:17 AM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

And what is the exact query you are using at the moment? Did you read and understand my message about INNER JOIN versus InnerJoin?

If this still doesn't make sense, can you post a CREATE script to create the tables and the code for the query?

Imar
__________________
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Follow me on Twitter

Author of Beginning ASP.NET 4.5 : in C# and VB, Beginning ASP.NET Web Pages with WebMatrix
and Beginning ASP.NET 4 : in C# and VB.
Did this post help you? Click the button below this post to show your appreciation!
 
Old May 12th, 2011, 10:07 AM
Authorized User
 
Join Date: May 2011
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Lightbulb joining 3 tables in a query

This is the query I am using

Code:
 Select StockCart.StockCartID, StockCart.Date, StockCart.Pendinding, StockCart.Direct, StockCart.IsShop, SUM[StockLineItem.QuantitySold, Level.UnitPrice] As Cost 
From StockCart InnerJoin StockLineItem On StockCart.StockCartID=StockLineItem.StockCartID InnerJoin Level 
On StockLineItem.LevelID=Level.LevelID
By the way I don't know how to create a scipt. I also didn't understand you on the post that you sent me on InnerJoin.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Joining 3 tables in a query sauloftarsus BOOK: Beginning ASP.NET 3.5 : in C# and VB BOOK ISBN: 978-0-470-18759-3 1 May 10th, 2011 12:22 PM
joining three tables nasirmunir MySQL 3 March 18th, 2009 02:31 PM
joining tables pit_bait SQL Language 2 September 28th, 2007 07:29 AM
joining 3 tables koskalgr MySQL 5 March 24th, 2006 09:52 AM
Joining two tables rajustha SQL Server 2000 1 December 7th, 2003 07:38 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.