Joining 3 tables in a query

sauloftarsus · May 13th, 2011, 10:27 AM

I tried it but that same exception was given me.

Imar · May 13th, 2011, 11:53 AM

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.....

sauloftarsus · May 13th, 2011, 12:52 PM

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

Imar · May 13th, 2011, 01:18 PM

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 · May 14th, 2011, 04:34 AM

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

Imar

sauloftarsus · May 14th, 2011, 08:09 AM

I don't understand

Imar · May 14th, 2011, 08:59 AM

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