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

[Guest]

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?

[duncancumming]

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

[Guest]

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>