Join expression not supported - using Access 2000 and CF

[Phil Powell]

<cfquery name="getAll" datasource="#re quest.dsn#">
SELECT U.userID, U.fname, U.lname, U.phone, U.lastLoggedIn,
U.choiceId, U.experience, T.label AS teamLabel, R.label AS
roleLabel
FROM User U LEFT JOIN UserTeamAssoc UTA
ON UTA.userID = U.userID,
Role R, UserRoleAssoc URA, Team T
WHERE U.userID = URA.userID
AND URA.roleID = R.roleID
AND U.userId > 1
ORDER BY #orderBy# #orderByTxt#
</cfquery>

This query produces the following error:

Join expression not supported.

Has anyone else run into this problem, if so, what can I do about it? I do
not have the ability to install Access on my machine so any solution could
not involve working with Access itself (I do have the .mdb file that I use
via ColdFusion App Server)

Phil

[Phil Powell]

<cfquery name="getAll" datasource="#re quest.dsn#">
SELECT
U.userID, U.fname, U.lname, U.phone, U.lastLoggedIn,
U.choiceId, U.experience, R.label AS roleLabel, T.label AS teamLabel
FROM
(((User U INNER JOIN UserRoleAssoc URA ON U.userId = URA.userID)
INNER JOIN Role R ON URA.roleId = r.roleID)
LEFT JOIN UserTeamAssoc UTA ON U.userID = UTA.userID)
LEFT JOIN Team T ON T.teamID = UTA.teamID
WHERE U.userID > 1
ORDER BY #orderBy# #orderByTxt#
</cfquery>

This is how I formed it and got a "too few parameters expected 1" error. Is
there anything you can do with this?

Phil
"Marc" <M.***********@ uva.nl> wrote in message
news:ae******** *************** **@posting.goog le.com...

"Phil Powell" <so*****@erols. com> wrote in message news:<dP1ab.183 646$xf.175404@l akeread04>... Phil,

Sorry, I forgot to mention another 3rd option, namely to split off the
left-outer join of the 2 tables and to put it in a seperate stored
query and to use that query as a table togeter with the other tables
in an inner-join or cartesian product with the Team table.

Marc

[Phil Powell]

Sorry, Bob, your query produced the following error:

Error Occurred While Processing Request
Error Executing Database Query.
[MERANT][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC
Microsoft Access Driver] Syntax error in JOIN operation.

The Error Occurred in
C:\CFusionMX\ww wroot\sowhat\qu ill\user.cfc: line 147
Called from C:\CFusionMX\ww wroot\sowhat\qu ill\user.cfm: line 114
Called from C:\CFusionMX\ww wroot\sowhat\qu ill\index.cfm: line 3
Called from C:\CFusionMX\ww wroot\sowhat\qu ill\user.cfc: line 147
Called from C:\CFusionMX\ww wroot\sowhat\qu ill\user.cfm: line 114
Called from C:\CFusionMX\ww wroot\sowhat\qu ill\index.cfm: line 3

145 : )
146 : ORDER BY #orderBy# #orderByTxt#
147 : </cfquery>
148 :
149 : <cfreturn getAll />
--------------------------------------------------------------------

SQL SELECT U.userID, U.fname, U.lname, U.phone,
U.lastLoggedIn, U.choiceId, U.experience, R.label AS roleLabel, T.label AS
teamLabel FROM ((((User U INNER JOIN UserRoleAssoc URA on U.userID =
URA.userID) INNER JOIN role R on URA.roleID = R.roleID) (LEFT JOIN
UserTeamAssoc UTA ON UTA.userID = U.userID INNER JOIN team t on T.teamID =
UTA.teamID ) ) ORDER BY U.lname, U.fname ASC
DATASOURCE quill
VENDORERRORCODE -3510
SQLSTATE 42000

Please Try The Following:

a.. Check the CFML Reference Manual to verify that you are using the
correct syntax.

b.. Search the Knowledge Base to find a solution to your problem.
Browser Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)
Remote Address 127.0.0.1
Referer
Date/Time 17-Sep-03 06:46 PM
Stack Trace (click to expand)
at
cfuser2ecfc1139 593935$funcGETA LL.runFunction( C:\CFusionMX\ww wroot\sowhat\qu i
ll\user.cfc:147 ) at
cfuser2ecfm1139 593977.runPage( C:\CFusionMX\ww wroot\sowhat\qu ill\user.cfm:11 4
) at
cfindex2ecfm985 72195.runPage(C :\CFusionMX\www root\sowhat\qui ll\index.cfm:3)
at
cfuser2ecfc1139 593935$funcGETA LL.runFunction( C:\CFusionMX\ww wroot\sowhat\qu i
ll\user.cfc:147 ) at
cfuser2ecfm1139 593977.runPage( C:\CFusionMX\ww wroot\sowhat\qu ill\user.cfm:11 4
) at
cfindex2ecfm985 72195.runPage(C :\CFusionMX\www root\sowhat\qui ll\index.cfm:3)
java.sql.SQLExc eption: [MERANT][SequeLink JDBC Driver][ODBC
Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error in JOIN
operation.
at com.merant.sequ elink.ssp.Diagn ostic.toSQLExce ption(Unknown Source)
at com.merant.sequ elink.ssp.Chain .cnvDiagnostics (Unknown Source)
at com.merant.sequ elink.ssp.Chain .decodeDiagnost ic(Unknown Source)
at com.merant.sequ elink.ssp.Chain .decodeBody(Unk nown Source)
at com.merant.sequ elink.ssp.Chain .decode(Unknown Source)
at com.merant.sequ elink.ssp.Chain .send(Unknown Source)
at com.merant.sequ elink.ctxt.stmt .StatementConte xt.send(Unknown Source)
at com.merant.sequ elink.ctxt.stmt .StmtCtxtState. _getDescribeInf o(Unknown
Source)
at com.merant.sequ elink.ctxt.stmt .CreatedStmtCtx t.getDescribeIn fo(Unknown
Source)
at com.merant.sequ elink.ctxt.stmt .StatementConte xt.getDescribeI nfo(Unknown
Source)
at com.merant.sequ elink.ctxt.stmt .StmtCtxtState. _execDirect(Unk nown Source)
at com.merant.sequ elink.ctxt.stmt .CreatedStmtCtx t.execDirect(Un known
Source)
at com.merant.sequ elink.ctxt.stmt .StatementConte xt.execDirect(U nknown
Source)
at com.merant.sequ elink.jdbc.Sequ eLinkStatement. _execute(Unknow n Source)
at com.merant.sequ elink.jdbc.Sequ eLinkStatement. execute(Unknown Source)
at jrun.sql.JRunSt atement.execute (JRunStatement. java:304)
at coldfusion.sql. Executive.execu teQuery(Unknown Source)
at coldfusion.sql. Executive.execu teQuery(Unknown Source)
at coldfusion.sql. SqlImpl.execute (Unknown Source)
at coldfusion.tage xt.sql.QueryTag .doEndTag(Unkno wn Source)
at
cfuser2ecfc1139 593935$funcGETA LL.runFunction( C:\CFusionMX\ww wroot\sowhat\qu i
ll\user.cfc:147 )
at coldfusion.runt ime.UDFMethod.i nvoke(Unknown Source)
at coldfusion.runt ime.UDFMethod$R eturnTypeFilter .invoke(Unknown Source)
at coldfusion.runt ime.UDFMethod$A rgumentCollecti onFilter.invoke (Unknown
Source)
at coldfusion.filt er.FunctionAcce ssFilter.invoke (Unknown Source)
at coldfusion.runt ime.UDFMethod.r unFilterChain(U nknown Source)
at coldfusion.runt ime.UDFMethod.i nvoke(Unknown Source)
at coldfusion.runt ime.TemplatePro xy.invoke(Unkno wn Source)
at coldfusion.runt ime.CfJspPage._ invoke(Unknown Source)
at
cfuser2ecfm1139 593977.runPage( C:\CFusionMX\ww wroot\sowhat\qu ill\user.cfm:11 4
)
at coldfusion.runt ime.CfJspPage.i nvoke(Unknown Source)
at coldfusion.tage xt.lang.Include Tag.doStartTag( Unknown Source)
at coldfusion.runt ime.CfJspPage._ emptyTag(Unknow n Source)
at
cfindex2ecfm985 72195.runPage(C :\CFusionMX\www root\sowhat\qui ll\index.cfm:3)
at coldfusion.runt ime.CfJspPage.i nvoke(Unknown Source)
at coldfusion.tage xt.lang.Include Tag.doStartTag( Unknown Source)
at coldfusion.filt er.CfincludeFil ter.invoke(Unkn own Source)
at coldfusion.filt er.ApplicationF ilter.invoke(Un known Source)
at coldfusion.filt er.PathFilter.i nvoke(Unknown Source)
at coldfusion.filt er.LicenseFilte r.invoke(Unknow n Source)
at coldfusion.filt er.ExceptionFil ter.invoke(Unkn own Source)
at coldfusion.filt er.ClientScopeP ersistenceFilte r.invoke(Unknow n Source)
at coldfusion.filt er.BrowserFilte r.invoke(Unknow n Source)
at coldfusion.filt er.GlobalsFilte r.invoke(Unknow n Source)
at coldfusion.filt er.DatasourceFi lter.invoke(Unk nown Source)
at coldfusion.CfmS ervlet.service( Unknown Source)
at jrun.servlet.Se rvletInvoker.in voke(ServletInv oker.java:106)
at jrun.servlet.JR unInvokerChain. invokeNext(JRun InvokerChain.ja va:42)
at
jrun.servlet.JR unRequestDispat cher.invoke(JRu nRequestDispatc her.java:241)
at
jrun.servlet.Se rvletEngineServ ice.dispatch(Se rvletEngineServ ice.java:527)
at jrun.servlet.ht tp.WebService.i nvokeRunnable(W ebService.java: 172)
at
jrunx.scheduler .ThreadPool$Dow nstreamMetrics. invokeRunnable( ThreadPool.java :
348)
at
jrunx.scheduler .ThreadPool$Thr eadThrottle.inv okeRunnable(Thr eadPool.java:45 1
)
at
jrunx.scheduler .ThreadPool$Ups treamMetrics.in vokeRunnable(Th readPool.java:2 9
4)
at jrunx.scheduler .WorkerThread.r un(WorkerThread .java:66)

This is the query:

<cfquery name="getAll" datasource="#re quest.dsn#">
SELECT
U.userID, U.fname, U.lname, U.phone, U.lastLoggedIn,
U.choiceId, U.experience, R.label AS roleLabel, T.label AS teamLabel
FROM ((((User U INNER JOIN UserRoleAssoc URA on U.userID = URA.userID)
INNER JOIN role R on URA.roleID = R.roleID)
(LEFT JOIN UserTeamAssoc UTA ON UTA.userID = U.userID
INNER JOIN team t on T.teamID = UTA.teamID
)
)
ORDER BY #orderBy# #orderByTxt#
</cfquery>

Hm.m...

Phil
"Bob Quintal" <bq******@gener ation.net> wrote in message
news:3c******** *************** *******@news.te ranews.com...

"Phil Powell" <so*****@erols. com> wrote in
news:dP1ab.1836 46$xf.175404@la keread04:

<cfquery name="getAll" datasource="#re quest.dsn#">
SELECT U.userID, U.fname, U.lname, U.phone,
U.lastLoggedIn,
U.choiceId, U.experience, T.label AS teamLabel,
R.label AS
roleLabel
FROM User U LEFT JOIN UserTeamAssoc UTA
ON UTA.userID = U.userID,
Role R, UserRoleAssoc URA, Team T
WHERE U.userID = URA.userID
AND URA.roleID = R.roleID
AND U.userId > 1
ORDER BY #orderBy# #orderByTxt#
</cfquery>

This query produces the following error:

Join expression not supported.

Has anyone else run into this problem, if so, what can I do
about it? I do not have the ability to install Access on my
machine so any solution could not involve working with Access
itself (I do have the .mdb file that I use via ColdFusion App
Server)

Phil

First you have no relation set from UTA to T. I've added what I
think you want
Second Access wants inner joins so marked, otherwise it produces
huge recordsets and then filters them through the where clause..

try:
From ((((User U INNER JOIN UserRoleAssoc URA
on U.userID = URA.userID)
INNER JOIN role R
on URA.roleID = R.roleID)
(LEFT JOIN UserTeamAssoc UTA
ON UTA.userID = U.userID
INNER JOIN team t
on T.teamID = UTA.teamID))

You may remove the now unnecessary where clauses.

If you get an error message try converting the last inner join to a
left join. Acess is sometimes picky about mixing inner and outer
joins.

Bob Q

[Pieter Linden]

"Phil Powell" <so*****@erols. com> wrote in message news:<dP1ab.183 646$xf.175404@l akeread04>...

<cfquery name="getAll" datasource="#re quest.dsn#">
SELECT U.userID, U.fname, U.lname, U.phone, U.lastLoggedIn,
U.choiceId, U.experience, T.label AS teamLabel, R.label AS
roleLabel
FROM User U LEFT JOIN UserTeamAssoc UTA
ON UTA.userID = U.userID,
Role R, UserRoleAssoc URA, Team T
WHERE U.userID = URA.userID
AND URA.roleID = R.roleID
AND U.userId > 1
ORDER BY #orderBy# #orderByTxt#
</cfquery>

This query produces the following error:

Join expression not supported.

You're trying to use a variation of something like Oracle SQL on
Access. Ain't gonna work. Ever. All joins in Access are in the FROM
clause, not the WHERE clause. Try building the query in Access and
then porting/pasting to CF.