Is there a left join in coldfusion query of queries?

Question

Since I'm new to using coldfusion according to my co-worker we can't do left join in a coldfusion query of queries. So this is what we would do a little "trick" to do a left join. example:

<cfquery datasource="Intranet" name="GroupStarsGiven">
    SELECT execoffice_status, submitterdept, COUNT(*) as 'totalstarsgiven'
    FROM CSEReduxResponses
    WHERE execoffice_status = 1
    GROUP BY execoffice_status, submitterdept
</cfquery>

<cfquery dbtype="query" name="GetTotalStarsGiven">
    SELECT *
    FROM GroupStarsGiven, GetDepartments
    WHERE GroupStarsGiven.submitterdept = GetDepartments.csedept_id
</cfquery>

<cfquery name="joinQuery2" dbtype="query" >
SELECT *
FROM GetTotalStarsGiven
WHERE GetTotalStarsGiven.csedept_id = -1
</cfquery>

<cfset QueryAddRow(joinQuery2)>

<cfquery name="GetUnion2" dbtype="query" >
SELECT *
FROM GetUnion, GetTotalStarsGiven
WHERE GetUnion.csedept_id = GetTotalStarsGiven.csedept_id

UNION

SELECT GetUnion.*, joinQuery2.*
FROM GetUnion, joinQuery2
WHERE GetUnion.csedept_id NOT IN (#ValueList(GetTotalStarsGiven.csedept_id)#)
ORDER BY csedept_name ASC
</cfquery

Is this the way to do left join in coldfusion? Just want to make sure since I not able to find a good article on this. thanks

Answer 1

Not exactly sure what you are trying to do here, but the answer to "there's no left join in ColdFusion?" is: it depends. You can certainly use a left join in a query to an external database, provided the database supports it (I can't think of a relational database that doesn't). But you can't do joins in a ColdFusion query-of-query; you have to resort to creative use of unions to accomplish that.

However, the need for a join in a query-of-query can usually be eliminated by effective use of joins in the initial query to the database. Exceptions to that would be if you needed to join queries from completely different ColdFusion data sources, or to the results of <cfdirectory>.

Answer 2

You are correct that there is no left outer join in ColdFusion's query of queries. The method you have shown is a common solution. If you are using the the ColdBox MVC Platform, there is actually a plugin that does left outer joins for you.

http://wiki.coldbox.org/wiki/Plugins:QueryHelper.cfm#doLeftOuterJoin

Disclaimer: I am part of Team ColdBox.