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 13th, 2011, 10:27 AM
Authorized User
 
Join Date: May 2011
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.
 
Old May 13th, 2011, 11:53 AM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 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!
 
Old May 13th, 2011, 12:52 PM
Authorized User
 
Join Date: May 2011
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
 
Old May 13th, 2011, 01:18 PM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 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!
 
Old May 14th, 2011, 04:34 AM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 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!
 
Old May 14th, 2011, 08:09 AM
Authorized User
 
Join Date: May 2011
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Lightbulb joining 3 tables in a query

I don't understand
 
Old May 14th, 2011, 08:59 AM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 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!





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.