Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- <cffunction name="OuterJoin" output="no" returntype="query">
- <cfargument name="QueryA" type="query" required="yes" />
- <cfargument name="QueryB" type="query" required="yes" />
- <cfargument name="joinColumn" type="string" required="yes" />
- <!-- QoQ doesn't like scoped queries --->
- <cfset var Query1 = Arguments.QueryA />
- <cfset var Query2 = Arguments.QueryB />
- <cfset var JoinQuery = '' />
- <cfset var Result = '' />
- <cfset var IgnoreIDs = '' />
- <cfquery dbtype="query" name="joinQuery">
- SELECT *
- FROM Query2
- WHERE Query2.#Arguments.JoinColumn# = <cfqueryparam value="-1" />
- </cfquery>
- <cfset QueryAddRow( joinQuery ) />
- <!--- Value List doesn't like dynamic things --->
- <cfset IgnoreIDs = '' />
- <cfloop query="Arguments.QueryB">
- <cfset IgnoreIDs = ListAppend( IgnoreIDs, Query2[ Arguments.JoinColumn][ Query2.CurrentRow ] ) />
- </cfloop>
- <cfquery dbtype="query" name="Local.Result">
- SELECT *
- FROM Query1, Query2
- WHERE Query1.#Arguments.JoinColumn# = Query2.#Arguments.JoinColumn#
- UNION
- SELECT Query1.*, JoinQuery.*
- FROM Query1, JoinQuery
- WHERE Query1.#Arguments.JoinColumn# NOT IN (<cfqueryparam value="#IgnoreIDs#" list="yes" />)
- </cfquery>
- <cfreturn Result />
- </cffunction>
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement