Need Help with Left Outer Join - Part II

Report · Mar 15, 2014

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.

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>

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.mob_id = edited.mob_id

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

</cfquery>

Simplified output version:

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

</cfloop>

</cfoutput>

What step am I missing?

Report · Mar 16, 2014

Suggestion:

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>

<cfif edit_id IS NOT "">#etitle#<cfelse>#title#</cfif>

</cfoutput>

Report · Mar 16, 2014

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

<CFMAIL

from="devotionals@mydaily-devotional.com"

to="#getclients.email#"

...

>

<cfif getdevotional.edit_id IS NOT "">#etitle#<cfelse>#title#</cfif>

</cfmail>

</cfoutput>

Do I need to have the getdevotional.contact_id to tie into the getclients.contact_id?

Report · Mar 16, 2014

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:

SELECT email, layoutInfo

FROM getClients

WHERE contact_id = #contact_id#

</cfquery>

</cfoutput>

</cfmail>

</cfoutput>

Report · Mar 22, 2014

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:

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.

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

#GetDevotional.etitle# #getDevotional.escripture#

#paragraphFormat(GetDevotional.ebody)#

<cfelse>

#GetDevotional.title# #getDevotional.scripture#

#paragraphFormat(GetDevotional.body)#

</cfif>

Thanks for your time.

Report · Mar 23, 2014

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:

#paragraphFormat(GetDevotional.ebody)#

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

Report · Mar 30, 2014

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.

Report · Mar 30, 2014

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.

Adobe Community

Need Help with Left Outer Join - Part II