Incorrect Syntax Error

I am getting an error saying, "Incorrect syntax near keyword 'ON'" Could someone please help? Here is my code:

str = "SELECT FAC_REQUEST.*, StateFAC.*, STATUS.sStatus, FAC_TYPE.Facility_Type FROM FAC_TYPE INNER JOIN ((FAC_REQUEST INNER JOIN STATUS ON FAC_REQUEST.iStatusID = STATUS.iStatusID) ON StateFAC.DVN = FAC_REQUEST.DVN) ON FAC_TYPE.Faciltiy_Type = FAC.FacilityType WHERE iRequestID=" & iRequestID

Thanks

INNER JOIN ((FAC_REQUEST INNER JOIN STATUS
ON FAC_REQUEST.iStatusID = STATUS.iStatusID)
AND StateFAC.DVN = FAC_REQUEST.DVN)
AND FAC_TYPE.Faciltiy_Type = FAC.FacilityType

I think the second and third ON's should be AND's...try that.

|||

I'm guessing at your database structure here since you are missing a table in your join, but I think you want something like this:

SELECT
 FAC_REQUEST.*,
 StateFAC.*,
 STATUS.sStatus,
 FAC_TYPE.Facility_Type
FROM FAC_TYPE
INNERJOIN FAC_REQUESTON StateFAC.DVN = FAC_REQUEST.DVN
INNERJOIN STATUSON FAC_REQUEST.iStatusID = STATUS.iStatusID
INNERJOIN FACON FAC_TYPE.Faciltiy_Type = FAC.FacilityType
WHERE iRequestID = 1

If this is not correct, you should format your code like I've done above as it will make it much easier you to spot the problem.

|||

This is all code I've inherited and there are several other SELECT statements similar to this that work. I've tried both suggestions above to no avail. I think the nesting works on the others by starting in the deepest nest and working out. I had a similar error next to the keyword 'FROM' and found a comma after from, removed it, and now I get this. Here's the code again if anyone has any other ideas.

str = "SELECT

FAC_REQUEST.*,

StateFAC.*,

STATUS.sStatus,

FAC_TYPE.Facility_Type

FROM

FAC_TYPE INNER JOIN (

(FAC_REQUEST INNER JOIN STATUS ON FAC_REQUEST.iStatusID = STATUS.iStatusID)

ON StateFAC.DVN = FAC_REQUEST.DVN)

ON FAC_TYPE.Faciltiy_Type = StateFAC.Facility_Type WHERE iRequestID=" & iRequestID

|||

Rather than just take out commas and/or brackets, you need to understand why they are there and also how you database structure interacts. Look at the database, see which tables exists and how they relate to each other. Start with the table that you want the base information from, and work outwards joining each table as you go. This will also help with your understanding of SQL in general.

If the above guesswork that I did above doesn't work, and you still don't understand the database, you will have to post the create scripts for the database so we can see how the tables are consrtucted and how they link together.

|||

Ok, I'm really stumped here so here are the scripts to the 4 tables in the SELECT statement.

TABLE 1 -- FAC_REQUEST:

/****** Object: Table [dbo].[FAC_REQUEST] Script Date: 09/19/2007 10:57:24 ******/

SET ANSI_NULLSON

GO

SET QUOTED_IDENTIFIERON

GO

CREATETABLE [dbo].[FAC_REQUEST](

[iRequestID] [int]IDENTITY(1,1)NOTNULL,

[DVN] [nvarchar](50)NULL,

[iAsmtTypeID] [int]NULL,

[iStatusID] [int]NOTNULLCONSTRAINT [DF__FAC_REQUE__iStat__720888E9]DEFAULT(1),

[dReceived] [datetime]NULLCONSTRAINT [DF__FAC_REQUE__dRece__73F0D15B]DEFAULT(getdate()),

[iRequestReceivedHowID] [int]NULLCONSTRAINT [DF__FAC_REQUE__iRequ__033314EB]DEFAULT(4),

[dCompleted] [datetime]NULL,

[d48HourCall] [datetime]NULL,

[i48HourCallByUniqueID] [int]NULLCONSTRAINT [DF__FAC_REQUE__i48Ho__023EF0B2]DEFAULT(0),

[iHostRegionID] [int]NULLCONSTRAINT [DF__FAC_REQUE__iHost__04273924]DEFAULT(7),

[sAgesServed] [nvarchar](50)NULLCONSTRAINT [DF__FAC_REQUE__sAges__051B5D5D]DEFAULT('0 to 12'),

[iPermitID] [int]NULLCONSTRAINT [DF__FAC_REQUE__iPerm__249408B6]DEFAULT(0),

[iRoomsInfants] [float]NULLCONSTRAINT [DF__FAC_REQUE__iRoom__1275587B]DEFAULT(0),

[iRoomsInfantsUnder12Months] [int]NULLCONSTRAINT [DF__FAC_REQUE__iRoom__07F7CA08]DEFAULT(0),

[iRoomsPreschool] [float]NULLCONSTRAINT [DF__FAC_REQUE__iRoom__13697CB4]DEFAULT(0),

[iRoomsSchoolAge] [float]NULLCONSTRAINT [DF__FAC_REQUE__iRoom__0AD436B3]DEFAULT(0),

[iRoomsHomes] [float]NULLCONSTRAINT [DF__FAC_REQUE__iRoom__08EBEE41]DEFAULT(0),

[dInitialContact] [datetime]NULL,

[dScheduleConversation] [datetime]NULL,

[iScheduleConversationByUniqueID] [int]NULL,

[iProgramStandards] [int]NULLCONSTRAINT [DF__FAC_REQUE__iProg__21B79C0B]DEFAULT(0),

[iEducationStandards] [int]NULLCONSTRAINT [DF__FAC_REQUE__iEduc__239FE47D]DEFAULT(0),

[iComplianceStandards] [int]NULLCONSTRAINT [DF__FAC_REQUE__iComp__1FCF5399]DEFAULT(0),

[iCapacity] [float]NULLCONSTRAINT [DF__FAC_REQUE__iCapa__25882CEF]DEFAULT(0),

[dInfoMailed] [datetime]NULL,

[iInfoMailedBy] [int]NULLCONSTRAINT [DF__FAC_REQUE__iInfo__1EDB2F60]DEFAULT(0),

[dFSRSent] [datetime]NULL,

[iFSRSentBy] [int]NULLCONSTRAINT [DF__FAC_REQUE__iFSRS__2864999A]DEFAULT(0),

[dScoreSheetMailed] [datetime]NULL,

[iScoreSheetMailedBy] [int]NULLCONSTRAINT [DF__FAC_REQUE__iScor__27707561]DEFAULT(0),

[iContactAttempts] [int]NULLCONSTRAINT [DF__FAC_REQUE__iCont__1645E95F]DEFAULT(0),

[iScheduleAttempts] [int]NULLCONSTRAINT [DF__FAC_REQUE__iSche__0DB0A35E]DEFAULT(0),

[iDelay] [int]NULLCONSTRAINT [DF__FAC_REQUE__iDela__0CBC7F25]DEFAULT(0),

[iCancellationPool] [int]NULLCONSTRAINT [DF__FAC_REQUE__iCanc__173A0D98]DEFAULT(2),

[iCancellationAttempts] [int]NULLCONSTRAINT [DF__FAC_REQUE__iCanc__2958BDD3]DEFAULT(0),

[dConfirm_Workload] [datetime]NULL,

[iConfirm_WorkloadBy] [int]NULLCONSTRAINT [DF_FAC_REQUEST_iConfirm_WorkloadBy]DEFAULT(0),

[sTracking_Code] [nvarchar](150)NULL,

[sTracking_UserName] [nvarchar](150)NULL,

[dCreated] [datetime]NULLCONSTRAINT [DF__FAC_REQUE__dCrea__0703A5CF]DEFAULT(getdate()),

[iCreatedBy] [int]NULLCONSTRAINT [DF__FAC_REQUE__iCrea__1DE70B27]DEFAULT(1),

[iSend_Asmt_Request_FAC] [tinyint]NULLCONSTRAINT [DF__FAC_REQUE__iSend__1551C526]DEFAULT(0),

[iSend_Asmt_Request_DCD] [tinyint]NULLCONSTRAINT [DF__FAC_REQUE__iSend__182E31D1]DEFAULT(0),

[iSend_Password_FAC] [tinyint]NULLCONSTRAINT [DF__FAC_REQUE__iSend__108D1009]DEFAULT(0),

[iSend_Password_DCD] [tinyint]NULLCONSTRAINT [DF__FAC_REQUE__iSend__1B0A9E7C]DEFAULT(0),

[iSend_Report_FAC] [tinyint]NULLCONSTRAINT [DF__FAC_REQUE__iSend__0EA4C797]DEFAULT(0),

[iSend_How_Consultant] [tinyint]NULLCONSTRAINT [DF__FAC_REQUE__iSend__0F98EBD0]DEFAULT(3),

[iSend_How_Provider] [tinyint]NULLCONSTRAINT [DF__FAC_REQUE__iSend__145DA0ED]DEFAULT(4),

[iFSR_WriteNoSend] [int]NULLCONSTRAINT [DF__FAC_REQUE__iFSR___1BFEC2B5]DEFAULT(0),

[iFSR_NoWriteNoSend] [int]NULLCONSTRAINT [DF__FAC_REQUE__iFSR___09E0127A]DEFAULT(0),

[iScheduler_iUniqueID] [int]NULLCONSTRAINT [DF__FAC_REQUE__iSche__22ABC044]DEFAULT(50),

[iProofreader_iUniqueID] [int]NULLCONSTRAINT [DF__FAC_REQUE__iProo__11813442]DEFAULT(50),

[iUniqueID_Provider] [int]NULLCONSTRAINT [DF_FAC_REQUEST_iUniqueID_Provider]DEFAULT(0),

[sFirst_Provider] [nvarchar](50)NULL,

[sLast_Provider] [nvarchar](50)NULL,

[iPositionID_Provider] [int]NULLCONSTRAINT [DF__FAC_REQUE__iPosi__20C377D2]DEFAULT(0),

[sAddress_Provider] [nvarchar](255)NULL,

[sCity_Provider] [nvarchar](255)NULL,

[sState_Provider] [nvarchar](255)NULL,

[sZip_Provider] [nvarchar](255)NULL,

[sAddressAlt_Provider] [nvarchar](255)NULL,

[sCityAlt_Provider] [nvarchar](255)NULL,

[sStateAlt_Provider] [nvarchar](255)NULL,

[sZipAlt_Provider] [nvarchar](255)NULL,

[sPhone_Provider] [nvarchar](50)NULL,

[sCell_Provider] [nvarchar](50)NULL,

[sFaxOffice_Provider] [nvarchar](50)NULL,

[sEmail_Provider] [nvarchar](150)NULL,

[iUniqueID_DCD] [int]NULLCONSTRAINT [DF__FAC_REQUE__iUniq__1CF2E6EE]DEFAULT(0),

[sFirst_DCD] [nvarchar](50)NULL,

[sLast_DCD] [nvarchar](50)NULL,

[iPositionID_DCD] [int]NULLCONSTRAINT [DF__FAC_REQUE__iPosi__1A167A43]DEFAULT(0),

[sPhone_DCD] [nvarchar](50)NULL,

[sCell_DCD] [nvarchar](50)NULL,

[sFaxOffice_DCD] [nvarchar](50)NULL,

[sEmail_DCD] [nvarchar](150)NULL,

[sAddress_DCD] [nvarchar](255)NULL,

[sCity_DCD] [nvarchar](50)NULL,

[sState_DCD] [nvarchar](50)NULL,

[sZip_DCD] [nvarchar](10)NULL,

[iLeadAssessor_iUniqueID] [int]NULLCONSTRAINT [DF__FAC_REQUE__iLead__267C5128]DEFAULT(0),

[iAssessmentID_Original] [int]NULLCONSTRAINT [DF__FAC_REQUE__iAsse__1922560A]DEFAULT(0),

[xiDaysDelayed] [int]NULL,

[dTemp_Transfer] [datetime]NULL,

[iPaid] [int]NULLCONSTRAINT [DF__FAC_REQUE__iPaid__0BC85AEC]DEFAULT(2),

[iPriorityID] [int]NULLCONSTRAINT [DF_FAC_REQUEST_iPriorityID]DEFAULT(2))ON [PRIMARY]

GO

TABLE 2 -- StateFAC

/****** Object: Table [dbo].[StateFAC] Script Date: 09/19/2007 10:59:35 ******/

SET ANSI_NULLSON

GO

SET QUOTED_IDENTIFIERON

GO

CREATETABLE [dbo].[StateFAC](

[sFacID] [int]NOTNULL,

[County] [nvarchar](50)NULL,

[DVN] [nvarchar](50)NULL,

[Facility_Type] [nvarchar](25)NULL,

[Facility_Name] [nvarchar](100)NULL,

[Street_Number] [nvarchar](25)NULL,

[Pre_Street_Direction] [nvarchar](25)NULL,

[Street_Name] [nvarchar](50)NULL,

[Street_Type] [nvarchar](25)NULL,

[Post_Street_Direction] [nvarchar](25)NULL,

[Unit] [nvarchar](50)NULL,

[P_O_Box_Number] [nvarchar](50)NULL,

[sCity] [nvarchar](50)NULL,

[sState] [nvarchar](2)NULL,

[sZip] [nvarchar](10)NULL,

[Plus4] [nvarchar](4)NULL,

[sPhone] [nvarchar](10)NULL,

[Extension] [nvarchar](10)NULL,

[MailAddress] [nvarchar](100)NULL,

[MailCity] [nvarchar](50)NULL,

[MailState] [nvarchar](2)NULL,

[MailZip] [nvarchar](11)NULL,

[MailZipPlus4] [nvarchar](4)NULL

)ON [PRIMARY]

TABLE 3 -- STATUS

/****** Object: Table [dbo].[STATUS] Script Date: 09/19/2007 11:01:02 ******/

SET ANSI_NULLSON

GO

SET QUOTED_IDENTIFIERON

GO

CREATETABLE [dbo].[STATUS](

[iStatusID] [int]IDENTITY(1,1)NOTNULL,

[sStatus] [nvarchar](255)NULL,

[xsComment] [ntext]NULL,

[iSort] [int]NULL

)ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

TABLE 4 -- FAC_TYPE

/****** Object: Table [dbo].[FAC_TYPE] Script Date: 09/19/2007 11:01:54 ******/

SET ANSI_NULLSON

GO

SET QUOTED_IDENTIFIERON

GO

CREATETABLE [dbo].[FAC_TYPE](

[iFacTypeID] [int]IDENTITY(1,1)NOTNULL,

[Facility_Type] [nvarchar](50)NULL

)ON [PRIMARY]

|||

SELECT FAC_REQUEST.*, StateFAC.*, STATUS.sStatus, FAC_TYPE.Facility_Type

FROM [FAC_REQUEST]INNERJOIN

[STATUS]ON [FAC_REQUEST].iStatusID= [STATUS].iStatusIDINNERJOIN

[StateFAC]ON [FAC_REQUEST].DVN= [StateFAC].DVNINNERJOIN

[FAC_TYPE]ON [StateFAC].Facility_Type= [FAC_TYPE].Facility_Type

WHERE [FAC_REQUEST].iRequestID= 1