 |
| 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
|
|
|
|
May 11th, 2011, 07:53 AM
|
|
Authorized User
|
|
Join Date: May 2011
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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.
|
|
May 11th, 2011, 09:47 AM
|
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
|
|
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.
================================================== =========
|
|
May 11th, 2011, 10:14 AM
|
|
Authorized User
|
|
Join Date: May 2011
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
joining 3 tables in a query
The table name is StockLineItem
|
|
May 11th, 2011, 10:46 AM
|
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
|
|
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.
================================================== =========
|
|
May 11th, 2011, 11:29 AM
|
 |
Wrox Author
|
|
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
|
|
Did you try INNER JOIN versus InnerJoin?
Imar
|
|
May 11th, 2011, 12:42 PM
|
|
Authorized User
|
|
Join Date: May 2011
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
joining 3 tables in a query
Yes I used InnerJoin between StockCart and StockLineItem and also between StockLineItem and Level.
|
|
May 11th, 2011, 12:55 PM
|
|
Wrox Author
|
|
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
|
|
Not sure what you mean. What exactly did you try? And did you read my post?
Imar
|
|
The Following User Says Thank You to Imar For This Useful Post:
|
|
|
May 12th, 2011, 08:15 AM
|
|
Authorized User
|
|
Join Date: May 2011
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|
|
May 12th, 2011, 08:17 AM
|
|
Wrox Author
|
|
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
|
|
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
|
|
May 12th, 2011, 10:07 AM
|
|
Authorized User
|
|
Join Date: May 2011
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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.
|
|
|
button below this post to show your appreciation!
