QoQ without a commond id

[jfb00]

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.

[BKBK · Accepted solution]

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>