Skip to main content
jfb00
Inspiring
Apr 27, 2015 3:47 PM UTC
Answered

QoQ without a commond id

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 is closed to new replies. Start a new post to keep the conversation going.
    BKBK
    BKBKCorrect answer
    Community Expert
    May 2, 2015 9:45 AM UTC

    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>

    View reply

    1 reply

    BKBK
    Community Expert
    BKBKCommunity ExpertCorrect answer
    Community Expert
    May 2, 2015 9:45 AM UTC

    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>

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