left join

[no_name_123]

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>

[WolfShade]

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

[Dave Ferguson]

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.

[BKBK]

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.