Copy link to clipboard
Copied
I have a devotional that goes out daily from a MOBILE table containing:
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:
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?
Copy link to clipboard
Copied
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)?
Copy link to clipboard
Copied
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>