Skip to main content
A
Anonymous
February 22, 2014
Question

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

  • February 22, 2014
  • 1 reply
  • 460 views

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?

    This topic has been closed for replies.

    1 reply

    D
    duncancumming
    Participating Frequently
    February 24, 2014

    We need to see more of your code, that deals with the results from your getDevotional query.  Have you got some logic that determines whether to use 'title' or 'etitle' (etc)?

    Also you're looping over getClients, but there's no reference to that in your getDevotional query - shouldn't you have something in the SQL that joins it to each church (which I presume is what comes from getClients)?

    A
    Anonymous
    February 24, 2014

    Yes the getClients grabs all information pertaining to the client and their layout 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 = 3

    </cfquery>

    Below is the logic I used to determine which version to use:

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

    Firefly Community Gallery
    Adobe Firefly

    Remix with Firefly Community Gallery

    Thousands of free creations to fall in love with and remix in Firefly.

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