Inner Join Syntax Error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Inner Join Syntax Error

Author

Topic

meanmyrlin
Starting Member

15 Posts

Posted - 2008-06-10 : 17:51:31

I am getting the error message:

Msg 156, Level 15, State 1, Line 62
Incorrect syntax near the keyword 'inner'.
Msg 102, Level 15, State 1, Line 62
Incorrect syntax near 'Code'.

When I attempt to run the following query:

select NewUsed, VehicleYear, VehicleMake, VehicleModel, VehicleTrim,
AppZip, ltrim(newtrademake) AS TradeMake, ltrim(trademodel)TradeModel,
TradeYear, NcodeL AS TradeNcodeL, NcodeM AS TradeNcodeM, OwingOnTrade, TradeAllowance, NetTradeIn

from
(select *,

Case

When TradeMake = ' Ford' and (Trademodel like '%Aerostar%' or Trademodel like '%Bronco%')
Then 'FORD TRUCKS'

Else ltrim(TradeMake)
End

AS NewTradeMake
from dbo.vw_dds)

inner join (select distinct make, model, ncodel, ncodem, modelyear from us.dbo.algmaster) Code

on code.make = ltrim(newtrademake) and code.model= ltrim(trademodel) and code.modelyear = tradeyear

The error is related to the inner join but I can't seem to figure out what I am doing wrong.

Thank you for your help,
Tasha

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-10 : 17:56:31

If you cared to reformat your query for easier reading, the error is well spotted.

select		NewUsed,
		VehicleYear,
		VehicleMake,
		VehicleModel,
		VehicleTrim,
		AppZip,
		ltrim(newtrademake) AS TradeMake,
		ltrim(trademodel) as TradeModel,
		TradeYear,
		NcodeL AS TradeNcodeL,
		NcodeM AS TradeNcodeM,
		OwingOnTrade,
		TradeAllowance,
		NetTradeIn
from		(
			select	*,
				Case
					When TradeMake = ' Ford' and (Trademodel like '%Aerostar%' or Trademodel like '%Bronco%') Then 'FORD TRUCKS'
					Else ltrim(TradeMake)
				End AS NewTradeMake
			from	dbo.vw_dds
		)  as {missing alias name here}
inner join	(
			select distinct	make,
					model,
					ncodel,
					ncodem,
					modelyear
			from		us.dbo.algmaster
		) as Code on code.make = {missing alias name here}.newtrademake
			and code.model= {missing alias name here}.trademodel
			and code.modelyear = {missing alias name here}.tradeyear

E 12°55'05.25"
N 56°04'39.16"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-10 : 17:56:42

First, you can't use * in your derived table. You must explicitly write out the columns to be returned.

Second, you need to alias the first derived table.

You need to start formatting your queries better so that they are easier to read and then post them using code tags. I'll show you yours in the next post.

EDIT: Nevermind Peter beat me to the formatting issue.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-10 : 17:58:39

[code]declare @b table (i int, j int)

insert @b values (1, 2)

select q.i,
q.j
from (
select *
from @b
) AS q[/code]

E 12°55'05.25"
N 56°04'39.16"

meanmyrlin
Starting Member

15 Posts

Posted - 2008-06-10 : 17:59:55

Thank you for the suggestions. I am new to this forum and apologize if my formatting is not up to parr with other, more experienced users.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-10 : 18:01:18