left outer join on query of query function

hi, 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? thanks

----- Excess quoted text cut - see Original Post for more ----- 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!

thanks Dave, i basically have just copied and pasted the code from the link i pasted above: do you have any idea how to rectify this or even if this would work. the guy indicates that his code is the finished product but now that credibility is lost, so just wondering whether you think it is worth me spending my time really trying to understand what is going on here thanks

> i basically have just copied and pasted the code from the > link i pasted above: do you have any idea how to rectify this > or even if this would work. I hadn't read the link, just looked at your code. Not having read the code, I didn't realize that joinQuery would contain a single record with empty strings as values. So, presumably, that would take care of the Cartesian product problem. If I had to guess, it should be something like this: <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> The only difference above is that I'm fetching all the columns of joinQuery. 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!

i see, thanks for your help dave, i am actually trying to do this on more than 1 primary key and it is not producing any errors when i add the joinquery.* but it is producing 2 times more rows than i expect, so it must be me missing a where clause somewhere. seeing as you also indicate this should be working i am going to go through it thoroughly to try to understand it, as outer joins are really needed in query of queries!!! thanks Dave ----- Excess quoted text cut - see Original Post for more -----

> i see, thanks for your help dave, i am actually trying to do > this on more than 1 primary key and it is not producing any > errors when i add the joinquery.* but it is producing 2 times > more rows than i expect, so it must be me missing a where > clause somewhere. Not necessarily. How many records are in joinQuery? It should just have one, empty record. The point of the first query is to get the columns, but not to select any records; the empty record is created directly below it. 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!