Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2005 > SQL Server 2005
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old May 11th, 2011, 07:53 AM
Authorized User
Points: 56, Level: 1
Points: 56, Level: 1 Points: 56, Level: 1 Points: 56, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: May 2011
Location: Accra
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.
Reply With Quote
  #2 (permalink)  
Old May 11th, 2011, 09:47 AM
Friend of Wrox
Points: 7,395, Level: 36
Points: 7,395, Level: 36 Points: 7,395, Level: 36 Points: 7,395, Level: 36
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Capital Federal, , Argentina.
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.
================================================== =========
Reply With Quote
  #3 (permalink)  
Old May 11th, 2011, 10:14 AM
Authorized User
Points: 56, Level: 1
Points: 56, Level: 1 Points: 56, Level: 1 Points: 56, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: May 2011
Location: Accra
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Lightbulb joining 3 tables in a query

The table name is StockLineItem
Reply With Quote
  #4 (permalink)  
Old May 11th, 2011, 10:46 AM
Friend of Wrox
Points: 7,395, Level: 36
Points: 7,395, Level: 36 Points: 7,395, Level: 36 Points: 7,395, Level: 36
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Capital Federal, , Argentina.
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.
================================================== =========
Reply With Quote
  #5 (permalink)  
Old May 11th, 2011, 11:29 AM
Imar's Avatar
Wrox Author
Points: 72,073, Level: 100
Points: 72,073, Level: 100 Points: 72,073, Level: 100 Points: 72,073, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,587 Times in 1,563 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!
Reply With Quote
  #6 (permalink)  
Old May 11th, 2011, 12:42 PM
Authorized User
Points: 56, Level: 1
Points: 56, Level: 1 Points: 56, Level: 1 Points: 56, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: May 2011
Location: Accra
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.
Reply With Quote
  #7 (permalink)  
Old May 11th, 2011, 12:55 PM
Imar's Avatar
Wrox Author
Points: 72,073, Level: 100
Points: 72,073, Level: 100 Points: 72,073, Level: 100 Points: 72,073, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,587 Times in 1,563 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!
Reply With Quote
The Following User Says Thank You to Imar For This Useful Post:
bharathkumarbs.sidd (October 25th, 2011)
  #8 (permalink)  
Old May 12th, 2011, 08:15 AM
Authorized User
Points: 56, Level: 1
Points: 56, Level: 1 Points: 56, Level: 1 Points: 56, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: May 2011
Location: Accra
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
Reply With Quote
  #9 (permalink)  
Old May 12th, 2011, 08:17 AM
Imar's Avatar
Wrox Author
Points: 72,073, Level: 100
Points: 72,073, Level: 100 Points: 72,073, Level: 100 Points: 72,073, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,587 Times in 1,563 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!
Reply With Quote
  #10 (permalink)  
Old May 12th, 2011, 10:07 AM
Authorized User
Points: 56, Level: 1
Points: 56, Level: 1 Points: 56, Level: 1 Points: 56, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: May 2011
Location: Accra
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.
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

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
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 05:46 AM.


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