Skip to main content
N
no_name_123
Known Participant
April 11, 2014
Question

left join

  • April 11, 2014
  • 3 replies
  • 640 views

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>
    This topic has been closed for replies.

    3 replies

    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    April 12, 2014

    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.

    Dave Ferguson
    Dave Ferguson
    Participating Frequently
    April 11, 2014

    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.

    WolfShade
    WolfShade
    Legend
    April 11, 2014

    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..

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