Highlighted

QoQ without a commond id

Advisor ,
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.

Adobe Community Professional
Correct answer by BKBK | Adobe Community Professional

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>

Views

148

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

QoQ without a commond id

Advisor ,
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.

Adobe Community Professional
Correct answer by BKBK | Adobe Community Professional

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>

Views

149

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
Apr 27, 2015 0
Adobe Community Professional ,
May 02, 2015

Copy link to clipboard

Copied

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
Reply
Loading...
May 02, 2015 0