1 Reply Latest reply on May 2, 2015 2:45 AM by BKBK

    QoQ without a commond id

    jfb00 Level 3

      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.

        • 1. Re: QoQ without a commond id
          BKBK Adobe Community Professional & MVP

          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>