3 Replies Latest reply on Apr 12, 2014 4:20 AM by BKBK

    left join

    no_name_123 Level 1

      Being new to coldfusin i have some question about this query .

      why do we need the first select (joinquery) ?

      is this a left join , just by looking at it it looks weird?

      Is there other ways to do letf join in coldfusion?

       

      <cfquery name="joinQuery" dbtype="query" >
      SELECT *
      FROM GetCardlyticsRedemptionStats
      WHERE GetCardlyticsRedemptionStats.SourceCustomerIDINT = -1
      </cfquery>
      
      
      <cfset QueryAddRow(joinQuery) />
      
      <cfquery name="CombineBothTables0" dbtype="query" >
      SELECT *
      FROM  GetCardlyticsID, GetCardlyticsRedemptionStats
      WHERE GetCardlyticsRedemptionStats.SourceCustomerIDINT = GetCardlyticsID.cardlyticscustomerid 
      
      UNION
      
      SELECT GetCardlyticsID.*, joinQuery.*
      FROM GetCardlyticsID, joinQuery
      WHERE GetCardlyticsID.cardlyticscustomerid NOT IN (#ValueList(GetCardlyticsRedemptionStats.SourceCustomerIDINT)#)
      
      
      </cfquery>
      
        • 1. Re: left join
          WolfShade Level 4

          I'm guessing this is for a class.

           

          The "joinQuery" is just getting everything in GetCardlyticsRedemptionStats where SourceCustomerIDINT equals -1.

           

          Then a row is added to joinQuery.

           

          Next, another query will get everything from two tables (one table being present in joinQuery), then it UNIONS that an almost identical query that replaces the second table with the joinQuery.

           

          Kinda scratchin' my head on why.. but, okay.. it looks, to me, as if this is going to return a query object named CombineBothTables0 that will have duplicates of everything.

           

          I give up trying to understand this one.. sorry..

          • 2. Re: left join
            Dave Ferguson Level 3

            I would be happy to help.. but I have no idea what you are trying to accomplish here.  This code is just riddled with issues and I have no idea where to even start.

            • 3. Re: left join
              BKBK Adobe Community Professional & MVP

              no_name_123 wrote:

              why do we need the first select (joinquery) ?

              The attribute dbtype="query" tells us that joinQuery is a so-called query-of-a-query. As WolfShade says, it is just getting everything from the original query GetCardlyticsRedemptionStats, where SourceCustomerIDINT equals -1.

               

              is this a left join , just by looking at it it looks weird?

              No, it is not a join. A join typically either uses the SQL keyword 'join' or includes, in the where-clause, a statement like 'table_1.someColumnName=table_2.someColumnName' or, in the case of query-of-a-query, 'query_1.someColumnName=query_2.someColumnName'. (ColdFusion's query-of-a-query concept does not support the 'join' keyword). Thus, the first select-query in CombineBothTables0 is a query-of-a-query join.

               

              Is there other ways to do letf join in coldfusion?

              Of course, there are several ways of doing a left join, but not as in the queries you've given. Strictly speaking, ColdFusion has nothing to do with SQL joins. The exception is query-of-a-query, which is a ColdFusion concept. SQL joins are a matter for the database server.