Skip to main content
P
PeytonT
Inspiring
March 4, 2008
Question

Does CFSQL Not Do Left Joins?

  • March 4, 2008
  • 3 replies
  • 769 views
Hello. The following syntax works fine inside a <CFQUERY> statement getting data from an ODBC database (where JobList and Reported are tables in the datasource named in the CFQUERY statement):

select * from JobList j left join Reported r on j.job_no = r.jobno

However, it does not work when embedded inside a <CFQUERY> statement trying to join two existing queries (i.e. dbtype = 'QUERY', where JobList and Reported are the names of the existing queries.

Instead I receive the error message:

Query Of Queries syntax error. Encountered "j.

If I remove the j and r aliases and go with select * from JobList left join Reported on JobList.job_no = Reported.jobno, I receive the error message:

Query Of Queries syntax error. Encountered "left

It does not help matters to add the word 'outer' after the word 'left'.

Should I be using a different syntax, or does CFSQL simply not do outer joins? The documentation says it does joins, but provided no examples.

If CFSQL simply cannot to outer joins, am I stuck? The two tables to be joined ultimately reside in completely different DB's: FoxPro and SQL Server.

Thanks for your help.



This topic has been closed for replies.

3 replies

N
Newsgroup_User
Inspiring
March 4, 2008
> It is documented, somewhere.

Strangely enough, it's in the "Query of Queries user guide" section of the
Developer Guide. Who would have thought, eh? ;-)

http://livedocs.adobe.com/coldfusion/8/htmldocs/using_recordsets_7.html

--
Adam
N
Newsgroup_User
Inspiring
March 4, 2008
NO Query of Query does not support outer joins, only inner joins. A
larger limitation I hope will one day be eliminated.

It is documented, somewhere. But I always forget about this limitation
until I once again try to use it.

P
paross1
Participating Frequently
March 4, 2008
Using joins - Q-of-Q

Using joins

A join operation uses a single SELECT statement to return a result set from multiple, related tables, typically those with a primary key - foreign key relationship. There are two SQL clauses that perform joins:

WHERE clause ColdFusion supports joins through a WHERE clause.

INNER JOIN and OUTER JOIN ColdFusion does not support joins through INNER JOIN or OUTER JOIN clauses.
Note: Query of Queries supports joins between two tables only.

FYI, you can sometimes accomplish your OUTER JOINs by using UNIONs as a work around.

Phil
P
PeytonTAuthor
Inspiring
March 4, 2008
I should point out that I am referring only to the syntax used in the above examples. 'Joblist' and 'Reported' are indeed the names of the queries I'm trying to join via CFSQL, but not the names of the tables in SQL Server in the syntax which works where both tables are there (i.e., a regular query, not a query of a query.
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices