left outer join in query of query

[hofar...@houseoffusion.com]

All:

I am on CF 8 now. Does anyone know if CF 9 supports left outer join in
query of query? CF 8 doesn't allow left outer join.

Nathan Chen

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:338524

[hofar...@houseoffusion.com]

Hi Nathan,
Both cf8 and cf9 allow for left outer joins when doing normal queries
however as far as I know query of query's does not support joins.

On a side note though, my general rule of thumb is if I need to use a query
of queries for anything other than changing the order of my structure, than
I really need to evaluate my original SQL query and looped query of queries
are big programming no no.

Just my 2 cents.. : )

Paul Alkema
http://paulalkema.com/

All:

Nathan Chen

Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:338526

[hofar...@houseoffusion.com]

You can actually use the old SQL syntax to do inner joins in a qoq,
haven't gotten a left join to work though.
this fails:
<cfquery dbtype="query" name="tmp">
SELECT *
FROM t1 inner join t2
ON t1.id = t2.id
</cfquery>
this works though:
<cfquery dbtype="query" name="tmp">
SELECT *
FROM t1, t2
WHERE t1.id = t2.id
</cfquery>

~Mahcsig

Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:338527

[hofar...@houseoffusion.com]

You may find this article useful.

http://instantbadger.blogspot.com/2006/07/faking-left-outer-join-in-query-of
.html

Paul Alkema
http://paulalkema.com/

~Mahcsig

Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:338529