Arg! ... Someone explain Joins 1

[TamedTech]

Hello Guys,

I'm really struggling with any form of joins at the moment, maybe its a lack of understand but i'm not sure.

I've got another problem that i'm looking for a little help with.

Basicly i'm looking to query my tables for a list of my schedules for each event, then return the campaign details, message details, and unit details that they apply to.

I've got this almost working, however i'm unable to filter the results by iether MessageID or CampaignID, i'm guessing its due to the organisation of my JOINS, but i'm not knowledgable enough to know.

Here is my query at present, but this just returns a list off details for all entries in the tblMessageCampaignScheduleLink table, rather than filtering out according to the WHERE clauses on MessageID and CampaignID.

			<cfquery name="qGetMessageSchedule" datasource="#application.dsn#">
				SELECT		tblTxtMessage.id,
							tblTxtMessage.message_title,
							tblTxtMessage.message_content,
							tblMessageCampaignScheduleLink.messageID,
							tblMessageCampaignScheduleLink.campaignID,
							tblMessageCampaignScheduleLink.startDate,
							tblMessageCampaignScheduleLink.endDate,
							tblMessageCampaignScheduleLink.startTime,
							tblMessageCampaignScheduleLink.endTime,
							tblCampaigns.campaignName,
							tblCampaigns.id,
							tblUnits.box_id,
							tblUnits.unit_name,
							tblUnitCampaignLink.campaignID,
							tblUnitCampaignLink.unitID
				FROM		tblMessageCampaignScheduleLink
				LEFT JOIN	tblTxtMessage
				ON			tblMessageCampaignScheduleLink.messageID = tblTxtMessage.id
				LEFT JOIN	tblUnitCampaignLink
				ON			tblMessageCampaignScheduleLink.campaignID = tblUnitCampaignLink.campaignID
				LEFT JOIN	tblUnits
				ON			tblUnitCampaignLink.unitID = tblUnits.box_id
				LEFT JOIN	tblCampaigns
				ON			tblUnitCampaignLink.unitID = tblCampaigns.id
				<cfif IsDefined("arguments.aMessageID")>
				WHERE		tblMessageCampaignScheduleLink.messageID = #arguments.aMessageID#
				</cfif>
				<cfif IsDefined("arguments.aCampaignID")>
				AND			tblMessageCampaignScheduleLink.campaignID = #arguments.aCampaignID#
				</cfif>
				AND			tblMessageCampaignScheduleLink.startDate between current_date
         and date_add(current_date, interval 7 day) 
		 		OR tblMessageCampaignScheduleLink.endDate between current_date
         and date_add(current_date, interval 7 day)
			</cfquery>

Hope you can help chaps, I know i've been posting alot recently but I've thrown myself in the deep and in the hope i'll learn how to swim a little quicker.

Thanks,

Rob

 

[TonyGroves]

The sequence of joins is as written; the first join is done first, then the result of that is used as the left part of the next join, etc. Also, I notice that your WHERE condition has ANDs and an OR with no brackets to clarify the sequence; maybe adding brackets might help clear things up.

 

[TamedTech]

Tony, you're a Gem,

I started by placing brackets into those WHERE clause and it now behaves as i would expect it too.

I suspect this needs some major optimization so i'll adress that shortly, but for now it appears to get the job done.

Thanks,

Rob