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)#)

After much experimentation, I have finally managed to get Microsoft SQL 2005 Mirroring with automatic failover working with ColdFusion MX 6/7.

The following steps describe how to setup ColdFusion.

1. Install the latest version of the DataDirect drivers by following these instructions on the Adobe web site – Updated DataDirect JDBC drivers (version 3.5)
2. Create a new ColdFusion datasource of type “Other”.
3. Enter the following settings, replacing XXXs with your details;

You should now be able to manually failover the mirrored database and, after an initial ColdFusion connection reset error, your application to run as normal.

Simple when you now how!