QoQ without a commond id

Advisor ,
Apr 27, 2015 Apr 27, 2015

Copy link to clipboard

Copied

Hi All,

I use this technique for QoQ when I have a commond key:

How to do an OUTER JOIN in Query of Queries | BealeARTS

Now, I have data from two different table but the join is based on multiple fields as:

select *

from tableA, tableB

where tableA.field1 = tableB.field1

and tableA.field2 = tableB.field2

and tableA.field3 = tableB.field3

How can I do the other join in this case?

Thanks in advanced.

Views

157

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct Answer

Adobe Community Professional , May 02, 2015 May 02, 2015
You could use exactly the same strategy as in the reference you gave, then perhaps make it more the result more efficient.<cfquery name="joinQuery" dbtype="query" >SELECT *FROM QueryBWHERE QueryB.field1 IS NULL OR QueryB.field2 IS NULL OR QueryB.field3 IS NULL</cfquery><cfset QueryAddRow(joinQuery) /><cfquery name="result" dbtype="query" >SELECT *FROM QueryA, QueryBWHERE QueryA.field1 = QueryB.field1AND QueryA.field2 = QueryB.field2AND QueryA.field3 = QueryB.field3UNIONSELECT QueryA.*, joinQuery...

Likes

Translate

Translate
Adobe Community Professional ,
May 02, 2015 May 02, 2015

Copy link to clipboard

Copied

LATEST

You could use exactly the same strategy as in the reference you gave, then perhaps make it more the result more efficient.

<cfquery name="joinQuery" dbtype="query" >

SELECT *

FROM QueryB

WHERE QueryB.field1 IS NULL OR QueryB.field2 IS NULL OR QueryB.field3 IS NULL

</cfquery>

<cfset QueryAddRow(joinQuery) />

<cfquery name="result" dbtype="query" >

SELECT *

FROM QueryA, QueryB

WHERE QueryA.field1 = QueryB.field1

AND QueryA.field2 = QueryB.field2

AND QueryA.field3 = QueryB.field3

UNION

SELECT QueryA.*, joinQuery.*

FROM QueryA, joinQuery

WHERE QueryA.field1 NOT IN (#ValueList(QueryB.field1)#) AND QueryA.field2 NOT IN (#ValueList(QueryB.field2)#) AND QueryA.field3 NOT IN (#ValueList(QueryB.field3)#)

</cfquery>

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines