How to do an OUTER JOIN in Query of Queries
ColdFusion Query of Queries does not natively support OUTER JOINs. The following code demonstrates a work around to perform a LEFT OUTER JOIN between two CF queries, QueryA and QueryB.
i.e. To do this;
SELECT * FROM QueryA LEFT OUTER JOIN QueryB ON QueryA.ID = QueryB.ID
One can use;
<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>
A common use of an OUTER JOIN is to find the non-matching records between two record sets, a so called NULL Based OUTER JOIN.
e.g.
SELECT * FROM QueryA LEFT OUTER JOIN QueryB ON QueryA.ID = QueryB.ID WHERE QueryB.ID IS NULL
This situation can be more efficiently implemented in Query of Queries by using the following technique;
SELECT * FROM QueryA WHERE QueryA.ID NOT IN (#ValueList(QueryB.ID)#)
July 17th, 2007 at 11:52 pm
Doesn’t like
SELECT *,NULL,NULL
Query Of Queries syntax error.
Encountered “NULL. Incorrect Select List, Incorrect select column
BUT since I need a specific result and know what column types are needed, your code showed the way. THANKS
July 19th, 2007 at 9:56 pm
@ Pat
Thanks for spotting that.
I have updated the post to correct the issue. It makes it a bit more complicated, but at least it works
June 29th, 2008 at 11:32 pm
hi,
the code above is not the finished product right? i am trying to replicate it but on the line:
SELECT QueryA.*, joinQuery
joinquery is not a collumn therefore it is producing an error.
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
thanks
August 2nd, 2008 at 3:58 pm
@Richard
Yes that’s a typo, it should be SELECT QueryA.*, joinQuery.* I’ve updated the post.
The CROSS JOIN in the second query is used to include the empty columns from the joinQuery to the filtered results from QueryA. This is done to give the results set of a LEFT OUTER JOIN.
I should also state that this technique assumes that the joining columns (.ID) can never have a value of -1. e.g. They are auto-numbers of a primary key.