Skip to main content
J
jfb00
Inspiring
April 27, 2015
Answered

QoQ without a commond id

  • April 27, 2015
  • 1 reply
  • 333 views

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.

    This topic has been closed for replies.
    Correct answer BKBK

    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>

    1 reply

    BKBK
    Community Expert
    BKBKCommunity ExpertCorrect answer
    Community Expert
    May 2, 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 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>

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices