Skip to main content
R
rickclark54
Known Participant
March 15, 2014
Question

Need Help with Left Outer Join - Part II

  • March 15, 2014
  • 1 reply
  • 1101 views

I believe I am getting the hang of the left outer join, but I am not there yet.

I have the same devotional that goes out to several clients which have the option of making changes to the devotional before it goes out.

For example I have 3 clients and one of them changed the devotional. So two of them will get the original devotional and the third company will get the modified version.

I’ve been able to figure out how to get the modified version to go out, but the 2 other clients are getting the modified version instead of the original.

<!--- get subscribers timezone and layout options, ie: header and color combinations --->


<cfquery name="getClients" datasource="#application.dsn#">

select *

from (subscriber INNER JOIN contacts ON subscriber.contact_id = contacts.contact_id) INNER JOIN layout ON contacts.contact_id = layout.contact_id

where subscriber.timezone = '4'

</cfquery>

<!--- determine which version of the devotional is available for this day --->

<cfquery name="getDevotional" datasource="#application.dsn#">

SELECT mobile.mob_id, mobile.display_date, mobile.title, mobile.body, mobile.scripture,edited.edit_id,edited.contact_id,edited.etitle,edited.escripture,edited.ebody

<!--- Mobile table holds the devotionals & Edited table holds the altrered devotionals --->

FROM mobile

left outer join edited ON mobile.mob_id = edited.mob_id

where mobile.display_date = <cfqueryparam value ="#dateformat(now(), "YYYY-MM-DD")#" cfsqltype="cf_sql_date">

</cfquery>

Simplified output version:

<cfoutput>

<cfloop query="getClients">

<cfif getDevotional.edit_id GT "">#etitle#<cfelse>#title#</cfif>

</cfloop>

</cfoutput>

What step am I missing?

    This topic has been closed for replies.

    1 reply

    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    March 16, 2014

    Suggestion:

    <!--- Distinguish the columns by name --->

    <cfquery name="getDevotional" datasource="#application.dsn#">

    SELECT mobile.mob_id AS id, mobile.display_date AS display_date, mobile.title AS title, mobile.body AS body, mobile.scripture AS scripture, edited.edit_id AS edit_id, edited.contact_id AS contact_id, edited.etitle AS etitle, edited.escripture AS escripture, edited.ebody AS ebody

    FROM mobile

    LEFT OUTER JOIN edited ON mobile.mob_id = edited.mob_id

    WHERE mobile.display_date = <cfqueryparam value ="#dateformat(now(), "YYYY-MM-DD")#" cfsqltype="cf_sql_date">

    </cfquery>

    <!--- Simplify the combination of cfloop and cfoutput --->

    <!--- It is ambiguous to use 'GT' for string comparison --->

    <!--- Shouldn't the query be getDevotional, instead of getClients? --->

    <cfoutput query="getDevotional">

    <cfif edit_id IS NOT "">#etitle#<cfelse>#title#</cfif>

    </cfoutput>

    R
    rickclark54Author
    Known Participant
    March 16, 2014

    Sorry bkbk, maybe I am not clear enough on the getclients query.

    getClients query:

    It pulls the layout information by the contact_id,

    the subscribers emails,

    the time zone and feeds it to cfmail.

    getdevotional query:

    pulls in the devotional info, title, scripture and body

    <cfoutput query="getclients">

    <CFMAIL

        from="devotionals@mydaily-devotional.com"

        to="#getclients.email#"

       ...

    >

         <layout table pulled from getclients>

              <cfif getdevotional.edit_id IS NOT "">#etitle#<cfelse>#title#</cfif>

        

    <end layout table>

    </cfmail>

    </cfoutput>

    Do I need to have the getdevotional.contact_id to tie into the getclients.contact_id?

    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    March 16, 2014

    Your original question is about the Devotional table, not about the Client table!

    I have the same devotional that goes out to several clients which have the option of making changes to the devotional before it goes out.

    For example I have 3 clients and one of them changed the devotional. So two of them will get the original devotional and the third company will get the modified version.

    If you need the e-mail address, you could indeed join up the Client table with the Devotional table. I can think of 2 ways to do this.

    Firstly, you could extend the SQL for getDevotional by adding a second join to either the Subscriber or Contacts table. Secondly, you could take the getClients query as given, and reuse it by means of query-of-a-query. I prefer this option, as it involves reuse and is simpler. It goes like this:

    <cfset emailAddress = arrayNew(1)>

    <cfset layoutInfo = arrayNew(1)>

    <cfset index = 1>

    <cfoutput query="getDevotional">

        <!--- Obtain e-mail address and layout info using a query of a query --->

        <!--- I have assumed the column names in getClients are 'email' and 'layoutInfo' --->

        <cfquery name="getInfo" dbtype="query">

            SELECT email, layoutInfo

            FROM getClients

            WHERE contact_id = #contact_id#

        </cfquery>

        <!--- Store e-mail addresses and layout info in respective arrays --->

        <cfset emailAddress[index] = getInfo.email>

        <cfset layoutInfo[index] = getInfo.layoutInfo>

    </cfoutput>

    <!--- Use the array to add 2 new columns for e-mail and layout to getDevotional query. --->

    <cfset colNumber = QueryAddColumn(getDevotional, "devotionalEmail", "VarChar", emailAddress)>

    <cfset colNumber = QueryAddColumn(getDevotional, "devotionalLayout", "VarChar", layoutInfo)> 

    <!--- Send mail out --->

    <cfoutput query="getDevotional">

        <cfmail to="#devotionalEmail#">

            <!--- layout --->

        </cfmail>

    </cfoutput>

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