2 Replies Latest reply on Feb 24, 2014 3:44 AM by rickaclark54

    How do I use OUTER JOIN to email different message than the default

    rickaclark54

      I have a devotional that goes out daily from a MOBILE table containing:

      • mob_id
      • calendardate
      • title
      • scripture
      • body

      The same devotional goes out to 3 different churches.

      I have a second TABLE called EDITED that holds an edited version of the daily devotional, containing:

      • edit_id
      • calendardate
      • etitle
      • escripture
      • ebody

      The query should look in the edited table for a devotional first to see if it matches the current date then send it out. Otherwise it sends out the default devotional  that corresponds with the current date.

       

      I would like to send out in replacement of the default devotional  to the church that edited the devotional.

      I am using the following outer Join but it doesn’t work. It sends the same devotional to everybody.

       

      <cfoutput>

      <cfloop query="getClients">

       

      <!--- determine if there is an edited version of the devotional 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

      FROM mobile

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

       

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

       

      </cfquery>

       

      What am I missing?