RE: left outer join on query of query function

[Dave Watts]

> i have been following the following link that discusses how 
> to do a left outer join on a query of query:
> 
> http://www.bealearts.co.uk/blog/2007/06/20/how-to-do-an-outer-
> join-in-query-of-queries/
> 
> this is the code that is meant to do it:
> 
> <cfquery name=”joinQuery” dbtype=”query” > SELECT * 
> FROM QueryB WHERE QueryB.ID = -1 </cfquery>
> 
> <cfset QueryAddRow(joinQuery) />
> 
> <cfquery name=”result” dbtype=”query” > SELECT * FROM 
> QueryA, QueryB WHERE QueryA.ID = QueryB.ID
> 
> UNION
> 
> SELECT QueryA.*, joinQuery
> FROM QueryA, joinQuery
> WHERE QueryA.ID NOT IN (#ValueList(QueryB.ID)#) </cfquery>
> 
> however i am getting a problem on the following line:
> 
> SELECT QueryA.*, joinQuery
> 
> this is producing an error and doesnt make much sense as 
> surely we must be seleting something from this joinQuery 
> rather than saying just select it. the error it is producing is:
> 
> Query Of Queries runtime error.
> The select column reference [joinQuery] is not a column in 
> any of the tables of the FROM table list.
> 
> can anyone recognize what this should be?

Is there a field in the QueryB table named "joinQuery"? If not, you can't
have it in your SELECT clause.

Also, you don't specify a join condition in the second query of your UNION
statement, which means that it'll return a Cartesian product.

Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/

Fig Leaf Software provides the highest caliber vendor-authorized
instruction at our training centers in Washington DC, Atlanta,
Chicago, Baltimore, Northern Virginia, or on-site at your location.
Visit http://training.figleaf.com/ for more information!

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to 
date
Get the Free Trial
http://ad.doubleclick.net/clk;203748912;27390454;j

Archive: 
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:308287
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4