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
  #21 (permalink)  
Old May 13th, 2011, 10:27 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

I tried it but that same exception was given me.
Reply With Quote
  #22 (permalink)  
Old May 13th, 2011, 11:53 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

Once again: please post your code. We don't have your table structure (since you don't know how to create CREATE scripts - which is fine) but we can't recommend anything without seeing your actual code.....
__________________
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
  #23 (permalink)  
Old May 13th, 2011, 12:52 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

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

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

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

[code]
Select StockCart.StockCartID, StockCart.Date, StockCart.Pending, StockCart.Direct, StockCart.IsShop, SUM[StockLineItem.QuantitySold * Level.UnitPrice] As CostPrice
From StockCart Inner Join StockLineItem On StockCart.StockCartID=StockLineItem.StockCartID Inner Join Level
On StockLineItem.LevelID=Level.LevelID Group By StockCartID
Reply With Quote
  #24 (permalink)  
Old May 13th, 2011, 01:18 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

Sum is a function so you need to use () instead of []. To make this crystal clear: you need to replace [ with ( and ] with ).

Hope this helps. if it doesn't, create scripts that create the tables you're describing so we can test your query. If you use Management Studio, right-click a database and choose Create Scripts or something like that. The item may be in the All Tasks menu.... Otherwise, Google can certainly help....

Cheers,

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
  #25 (permalink)  
Old May 14th, 2011, 04:34 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

BTW, you also need to add the columns from your SELECT statement to the GROUP By statement.

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
  #26 (permalink)  
Old May 14th, 2011, 08:09 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

I don't understand
Reply With Quote
  #27 (permalink)  
Old May 14th, 2011, 08:59 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

Me neither......

If you want help on this forum, you need to help us help you. A comment such as "I don't understand" is not helpful at all. What do you not understand? How to replace [] with ()? How to add a group by? How to right-click a table and choose Script table as?

So, please, explain your problem in more detail. What did you try, what worked, what didn't work, what Google gave you when you searched for CREATE scripts and so on.

Here's an example of a CREATE script for a table:

Code:
CREATE TABLE [dbo].[Level]
(
 [LevelID] [int] IDENTITY(1,1) NOT NULL,
 [ProductName] [nvarchar](50) NOT NULL,
 [UnitPrice] [money] NOT NULL,
  CONSTRAINT [PK_Level] PRIMARY KEY CLUSTERED ( [LevelID] ASC )
)
If you create scripts for your other tables and post them here, we can actually TEST your query and be more helpful.

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
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 06:38 AM.


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