7 Replies Latest reply on Mar 30, 2014 6:00 AM by BKBK

    Need Help with Left Outer Join - Part II

    rickclark54 Level 1

      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?

        • 1. Re: Need Help with Left Outer Join - Part II
          BKBK Adobe Community Professional & MVP

          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>

          • 2. Re: Need Help with Left Outer Join - Part II
            rickclark54 Level 1

            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?

            • 3. Re: Need Help with Left Outer Join - Part II
              BKBK Adobe Community Professional & MVP

              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>

              • 4. Re: Need Help with Left Outer Join - Part II
                rickclark54 Level 1

                I'm sorry BKBK, by trying not to overwhelm you with information and unclear what I needed to do, I have left you in the dark about what I really need. So, I have gone into detail about what I have and what I need. I tried your last approach and really had no clue on how to resolve my problem.  Sorry for the length, but I really need to resolve this problem:

                 

                There are 3 Tables in the GETINFO QUERY which creates the layout, displays the company information, and supplies the subscribers info.

                 

                SUBSCRIBERS TABLE

                Supplies, email, firstname, timezone, and contact_id

                 

                CONTACT TABLE

                Supplies the information for social links, the footer, company name, unsubscribe information

                contact_id, company,city,state,zip,facebook,twitter,linkedin,youtube,website

                 

                LAYOUT TABLE

                27 fields that supplies  all info for header, footer, and color combinations  for the responsive email joined by the contact_id

                 

                My first query to get all email information:


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

                 

                There are 2 tables that supply the devotional

                 

                MOBILE TABLE

                Title, scripture, body, display_date

                This is the default table that supplies the daily devotional.

                 

                EDITED TABLE

                eTitle, eScripture, ebody, display_date

                Optional table that when info is  present REPLACES the MOBILE TABLE. A participating church can make changes to the daily devotional, where it is saved in the edited table. When the devotional goes out, the GETDEVOTIONAL QUERY searches for an edited version first. If present, it sends out the edited version. Otherwise the default devotional will go out.

                 

                By using a left outer join in the GETDEVOTIONAL QUERY, I can get the edited version to display, but it is displaying to everybody, not just the church that created the edited version.

                 

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

                 

                The output:


                <cfif getdevotional.edit_id NEQ "">


                <p style="font-family: Arial, Helvetica, sans-serif; font-size: 14px;font-weight: bold;color: #h2_color#;margin-top: 12px;margin-bottom: 4px;padding-bottom: 0px;">#GetDevotional.etitle#<br /> #getDevotional.escripture#</p>

                <p style="text-indent:15px;">

                #paragraphFormat(GetDevotional.ebody)#

                </p>

                 

                <cfelse>

                 

                <p style="font-family: Arial, Helvetica, sans-serif; font-size: 14px;font-weight: bold;color: #h2_color#;margin-top: 12px;margin-bottom: 4px;padding-bottom: 0px;">#GetDevotional.title#<br /> #getDevotional.scripture#</p>

                <p style="text-indent:15px;">

                #paragraphFormat(GetDevotional.body)#

                </p>

                 

                </cfif>

                 

                Thanks for your time.

                • 5. Re: Need Help with Left Outer Join - Part II
                  BKBK Adobe Community Professional & MVP

                  Nice of you to mention it, but there is really no need to say sorry. I think I understood, even if only roughly, what you are trying to do.

                   

                  The code you give here will display to every member. That is because the following code loops through, and outputs for, all IDs:

                   

                  <cfoutput query="getDevotional">

                  <cfif getdevotional.edit_id NEQ "">

                  <!--- Display for every member who did an edited --->

                  #paragraphFormat(GetDevotional.ebody)#

                  <cfelse>

                  <!--- Default display where there was no edit --->

                  #paragraphFormat(GetDevotional.body)#

                  </cfif>

                  </cfoutput>

                   

                  If you wish to display for just one member, then you should identify members, for example, by implementing login. You could then add the specific userID to the where-clause of the query.

                  • 6. Re: Need Help with Left Outer Join - Part II
                    rickclark54 Level 1

                    The idea is if church A changes the text, then the all subscribers to church A would get the modified email. Wheras church B would get the default email. My coding is wrong, but I don't know what to do to. I have played with various scenerios with no results.

                    • 7. Re: Need Help with Left Outer Join - Part II
                      BKBK Adobe Community Professional & MVP

                      Straightforward really. Firstly, do a query to get the church or churches that change the text. Secondly, do a query to get the subscribers of the church or churches from the first query. Done.