dynamic sql

I need a smart SQL person for this one.   I have a somewhat static table that I use to create colums in my resulting query.  I have a table with static data for each advertising order.  I have a table that relates to the advertising table and contains a variable number of contacts.  The code is in Coldfusion but that's pretty simple stuff.  My concern is that a record is missing for the results. For Ad id 197136 there should be a listing.   My biggest concern is this encredible query I've built.  I'm convinced there is a better way.


 client_services_contacts table client_services_contact_types
The resulting output page---------------------------------
<!--- panel13_ads.cfm --->
<cfinvoke component="globalcfc.basic" method="getContactsByIndiv" individ="#indivnum#" servicelineid="5" returnvariable="Qcontacts" />
<cfinvoke component="globalcfc.basic" method="getContactTypes" servicelineid="5" returnvariable="Qcontacts2x" />

<cfset QADdata = Application.objIndivBasic.GetAdvertising(CompanyNum = "#Val(users_Info.current_company_number)#",indivNum="#indivNum#") />
<div class="PanelTab">
	Advertising - 
	<span><a href="<cfoutput>#request.controlUrl#</cfoutput>advertising/index.cfm?fa=new" target="_blank">Add New</a></span>
</div>

<div class="PanelContent">
	<cfif QADdata.recordcount eq 0>
		No records for this service
	<cfelse>
		<cfgrid format="html" name="grid_Advertising" query="QADdata"  width="1000" selectonload="no" selectmode="row" autowidth="yes" colheaderalign="center" colheaders="yes" griddataalign="left"   selectcolor="##FF9900">
			<cfgridcolumn name="ID" header="ID" display="Yes" dataalign="left" width="10" href="#request.controlPath#advertising/index.cfm?fa=view&ad_id=#QADdata.ID#" target="_blank" />
			<cfgridcolumn name="package_id" header="Package" dataalign="left" width="14" target="_blank"  />
			<cfgridcolumn name="startdate" header="Date Opened" dataalign="left" width="14" target="_blank" />
			<cfgridcolumn name="enddate" header="Date Closed" dataalign="left" width="14"  target="_blank" />
			<cfgridcolumn name="fee" header="Total Fee" dataalign="left" width="10" target="_blank" />
			<cfgridcolumn name="marketer" header="Marketer" dataalign="left" width="20" target="_blank" />
			<cfloop query="Qcontacts2x">
			<cfgridcolumn name="#Qcontacts2x.contact_type_acronym#contactname" header="#Qcontacts2x.contact_type_acronym# Contact Name" dataalign="left" width="20" />
			</cfloop>
		</cfgrid>
	</cfif>
</div>


The main query----------------------------------------------
	<cffunction name="getAdvertising" output="false" returntype="query">
		<cfargument name="indivNum" required="No" type="string" default="" />
		<cfargument name="companyNum" required="No" type="string" default="" />
		<cfset var queryResults="" />
		<cfinvoke component="globalcfc.basic" method="getContactTypes" servicelineid="5" returnvariable="Qcontacts2" />
		<cfinvoke component="globalcfc.basic" method="getContactsByIndiv" individ="#indivnum#" servicelineid="5" returnvariable="Qcontacts" />
		<cfquery datasource="#request.dsn#" name="queryResults">
			SELECT  AD.ID, AD.package_id, ad.startdate, ad.enddate, ad.fee, au.name as marketer, ADdesc.description <cfloop query="QContacts2">,ui#Qcontacts2.contact_type_acronym#.lastname + ', ' + ui#Qcontacts2.contact_type_acronym#.firstname as #Qcontacts2.contact_type_acronym#contactname, #Qcontacts2.contact_type_acronym#.contact_id as #Qcontacts2.contact_type_acronym#contact_id</cfloop>
			FROM advertising as AD 
			LEFT JOIN admin_users as au on au.adminid = ad.marketer_id 
			LEFT JOIN advertising_components as Adc on Adc.advertising_id = ad.ID 
			LEFT JOIN advertising_descriptions as ADdesc on ADdesc.id = ADc.component_id 
			<cfloop query="Qcontacts2">
			left join client_services_contacts as #Qcontacts2.contact_type_acronym# on #Qcontacts2.contact_type_acronym#.contact_id = #arguments.indivnum# and #Qcontacts2.contact_type_acronym#.contacttypeid = #Qcontacts2.id# and #Qcontacts2.contact_type_acronym#.service_id = AD.ID
			left join client_services_contact_Types as #Qcontacts2.contact_type_acronym#types on #Qcontacts2.contact_type_acronym#types.id = #Qcontacts2.contact_type_acronym#.contacttypeid  
			LEFT OUTER JOIN Users_Info AS ui#Qcontacts2.contact_type_acronym# ON ui#Qcontacts2.contact_type_acronym#.ID = #Qcontacts2.contact_type_acronym#.contact_id
			</cfloop>
			WHERE 0=0 
			<cfif val(arguments.CompanyNum) NEQ "">
				AND ad.company_ID = 
				<cfqueryparam value="#arguments.companynum#" />
			</cfif>
			and (
			<cfif val(arguments.indivNum) NEQ "">
				<cfloop query="Qcontacts">
				 #Qcontacts.contact_ID# = #arguments.indivnum# or
				</cfloop>
				0=1)
			</cfif>
			ORDER BY ad.startdate DESC 
		</cfquery>
		<cfreturn queryResults />
	</cffunction>

The supporting queries------------------------------------
	<cffunction name="getContactTypes" returntype="query" access="remote">
		<cfargument name="servicelineID" required="yes" />
		<cfquery datasource="#request.dsn#" name="contacttypes">
			select * from client_services_contact_types where servicelineid = #val(arguments.servicelineid)# order by id 
		</cfquery>
		<cfreturn contacttypes />
	</cffunction>


	<cffunction name="getContactsByIndiv" returntype="query" access="remote">
		<cfargument name="indivID" required="yes" />
		<cfargument name="servicelineID" required="yes" />
		<cfquery datasource="#request.dsn#" name="contactsByIndiv">
			SELECT csc.contact_ID, csc.contactTypeID, csc.active, csc.date_added, csct.contact_Type_Acronym,  csct.id as contacttypesid, ui.lastname + ', ' + ui.firstname as contactname ,csc.service_id  
			FROM Client_Services_Contacts as csc
			LEFT OUTER JOIN Users_Info AS ui ON ui.ID = csc.contact_id
			left join client_services_contact_types as csct on csct.id = csc.contacttypeid   
			WHERE (csct.ServiceLineID = #val(arguments.serviceLineID)# and csc.contact_id = #val(arguments.indivID)# AND csc.active = 1) 
		</cfquery>
		<cfreturn contactsByIndiv />
	</cffunction>

	<cffunction name="getContacts" returntype="query" access="remote">
		<cfargument name="serviceID" required="yes" />
		<cfargument name="servicelineID" required="yes" />
		<!--- Query Statement --->
		<cfquery datasource="#request.dsn#" name="contacts">
			<!--- select csc.contact_id, csct.description, csc.contact_type_acronym, csc.active, csc.date_added, ui.lastname + ', ' + ui.firstname as contactname, csct.acronym
				from client_services_contacts as csc  inner join
				client_services_contact_types as csct on csct.servicelineid = '5' left join
				Users_info as ui on ui.id = csc.contact_id AND csc.contact_type_acronym = csct.acronym
				where csc.service_id = #arguments.serviceID# and csc.active =1
				order by csct.id --->
			SELECT ui.ID as uid, csct.description, csc.ContactTypeID, csc.active, csc.date_added, ui.LastName + ', ' + ui.FirstName AS contactname, csct.contact_type_acronym as acronym, csct.id AS csctid 
			FROM Client_Services_Contact_Types AS csct 
			LEFT OUTER JOIN Client_Services_Contacts AS csc ON csct.id = csc.contactTypeID AND csc.service_id = #val(arguments.serviceID)# AND csc.active = 1 
			LEFT OUTER JOIN Users_Info AS ui ON ui.ID = csc.contact_id 
			WHERE (csct.ServiceLineID = #val(arguments.serviceLineID)#) ORDER BY csctid 
		</cfquery>
		<cfreturn contacts />
	</cffunction>

Open in new window

Resulting-page-with-dumps.PNG
lantervjAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
gdemariaConnect With a Mentor Commented:
Since you're using all OUTER JOINS, then you would think all ADVERTISING records would appear.  However, your WHERE clause can prevent it.   If your WHERE clause is against any of your OUTER JOIN tables, then you prevent the records from showing....


WHERE 0=0
<cfif val(arguments.CompanyNum) NEQ "">
   ===== THIS IS OK BECAUSE IT IS CHECKING THE ADVERTISING TABLE ==========
   AND ad.company_ID = <cfqueryparam value="#arguments.companynum#" />
</cfif>
and (
<cfif val(arguments.indivNum) NEQ "">
 <cfloop query="Qcontacts">
    ====== ASSUMING THIS CRITERIA IS AGAINST AN OUTER JOIN TABLE, IT WILL KEEP THE ADVERTISING RECORD FROM SHOWING ========
   #Qcontacts.contact_ID# = #arguments.indivnum# or
  </cfloop>
0=1)
</cfif>


You need to put the where clause of any LEFT JOIN table as part of the JOIN ... ON... Condition.
That is IF you want it to stay OUTER JOINED.  

Putting the condition in the WHERE clause kills the OUTER join...

Hope that makes sense.


0
 
lantervjAuthor Commented:
I think I understand it.   But, to make it more clear,  this was originally written with the contact ids  embedded in the advertising table. I had to remove them and create a seperate table for a variable number of contacts but maintaining only 4 types of contacts.  The general idea here is to read all advertising contacts and look for just one individual and list all the ads he/she is part of.  I think I should have attacked this differently by collecting the contact records first.  
0
 
gdemariaCommented:

What table joins ADVERTISING and CONTACTS ?

0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
gdemariaCommented:

This line is confusing...

 #Qcontacts.contact_ID# = #arguments.indivnum# or

Does this mean you have a Column named after the ID?
 
  123 = 100

You have a column named 123 ?

How about CONTACT_ID = 100 ?


0
 
lantervjAuthor Commented:
This is how I ended up.  I was not qualifing the left side of the where clause to resolve to hr.contact_id = indivnum.  It appears to work now.
SELECT AD.ID, AD.package_id, ad.startdate, ad.enddate, ad.fee, au.name as marketer, ADdesc.description 
			<cfloop query="QContacts2">
				,ui#Qcontacts2.contact_type_acronym#.lastname + ', ' + ui#Qcontacts2.contact_type_acronym#.firstname as #Qcontacts2.contact_type_acronym#contactname, #Qcontacts2.contact_type_acronym#.contact_id as #Qcontacts2.contact_type_acronym#contact_id
			</cfloop>
			FROM advertising as AD 
			LEFT JOIN admin_users as au on au.adminid = ad.marketer_id 
			LEFT JOIN advertising_components as Adc on Adc.advertising_id = ad.ID 
			LEFT JOIN advertising_descriptions as ADdesc on ADdesc.id = ADc.component_id 
			<cfloop query="Qcontacts2">
				left join client_services_contacts as #Qcontacts2.contact_type_acronym# on #Qcontacts2.contact_type_acronym#.contact_id = #arguments.indivnum# and #Qcontacts2.contact_type_acronym#.contacttypeid = #Qcontacts2.id# and #Qcontacts2.contact_type_acronym#.service_id = AD.ID 			left join client_services_contact_Types as #Qcontacts2.contact_type_acronym#types on #Qcontacts2.contact_type_acronym#types.id = #Qcontacts2.contact_type_acronym#.contacttypeid 
				LEFT JOIN Users_Info AS ui#Qcontacts2.contact_type_acronym# ON ui#Qcontacts2.contact_type_acronym#.ID = #Qcontacts2.contact_type_acronym#.contact_id 
			</cfloop>
			WHERE 0=0 and (
				 
				<cfloop query="Qcontacts2">
					#Qcontacts2.contact_type_acronym#.contact_id = #arguments.indivnum# or
					</cfloop>
					0 = 1)
			ORDER BY ad.startdate DESC

Open in new window

0
 
gdemariaCommented:
I'm curious..

since you already have this statement in your LEFT JOIN clause..

 #Qcontacts2.contact_type_acronym#.contact_id = #arguments.indivnum#


what happens if you remove it from your WHERE clause at the bottom?
I would think the results would be the same.

But either way, glad it's working
0
 
lantervjAuthor Commented:
I tried that and got all the ad records in the table.  I was curious about that too.
0
 
gdemariaCommented:

if you don't want that, then I would suspect you could just change the join to an inner join..

<cfloop query="Qcontacts2">
   INNER join client_services_contacts .....

0
 
lantervjAuthor Commented:
I get zero records.

<cfloop query="Qcontacts2">
                        inner join client_services_contacts as #Qcontacts2.contact_type_acronym# on #Qcontacts2.contact_type_acronym#.contact_id = #arguments.indivnum# and #Qcontacts2.contact_type_acronym#.contacttypeid = #Qcontacts2.id# and #Qcontacts2.contact_type_acronym#.service_id = AD.ID                   left join client_services_contact_Types as #Qcontacts2.contact_type_acronym#types on #Qcontacts2.contact_type_acronym#types.id = #Qcontacts2.contact_type_acronym#.contacttypeid

WHERE 0=0

It doesn't make sense that it returns no records.
0
 
lantervjAuthor Commented:
The only things that works is to have the compare for the indivnum in both the left join and where clause.
0
 
gdemariaCommented:
it must be the other conditions of the join..

 it was worth a test I guess
0
 
lantervjAuthor Commented:
Once again, many thanks.
0
 
lantervjAuthor Commented:
I think I closed this too soon.  The attached queries give zero records and all records.  I can't remember what I might have changed.  I was testing with new data.
No data returned:
SELECT AD.ID, AD.package_id, ad.startdate, ad.enddate, ad.fee, au.name as marketer, ADdesc.description ,c.name as companyname
			<cfloop query="QContacts2">
				,ui#Qcontacts2.contact_type_acronym#.lastname + ', ' + ui#Qcontacts2.contact_type_acronym#.firstname as #Qcontacts2.contact_type_acronym#contactname, #Qcontacts2.contact_type_acronym#.contact_id as #Qcontacts2.contact_type_acronym#contact_id
			</cfloop>
			FROM advertising as AD 
			LEFT JOIN admin_users as au on au.adminid = ad.marketer_id 
			LEFT JOIN advertising_components as Adc on Adc.advertising_id = ad.ID 
			LEFT JOIN advertising_descriptions as ADdesc on ADdesc.id = ADc.component_id 
			LEFT JOIN companies as c on c.id = AD.company_id 
			<cfloop query="Qcontacts2">
				inner join client_services_contacts as #Qcontacts2.contact_type_acronym# on #Qcontacts2.contact_type_acronym#.contact_id = #arguments.indivnum# and #Qcontacts2.contact_type_acronym#.contacttypeid = #Qcontacts2.id# and #Qcontacts2.contact_type_acronym#.service_id = AD.ID 			inner join client_services_contact_Types as #Qcontacts2.contact_type_acronym#types on #Qcontacts2.contact_type_acronym#types.id = #Qcontacts2.contact_type_acronym#.contacttypeid 
				inner JOIN Users_Info AS ui#Qcontacts2.contact_type_acronym# ON ui#Qcontacts2.contact_type_acronym#.ID = #Qcontacts2.contact_type_acronym#.contact_id 
			</cfloop>
			WHERE 0=0 and (				 
				 <cfloop query="Qcontacts2">
					#Qcontacts2.contact_type_acronym#.contact_id = #arguments.indivnum# or
					</cfloop>
					0 = 1)
			ORDER BY ad.startdate DESC

This one returns 8 duplicates of the first ad record but only a single row of each of the other 2 ads (which wold be correct).

Open in new window

0
 
lantervjAuthor Commented:
Oops, forgot the second version of the code
SELECT AD.ID, AD.package_id, ad.startdate, ad.enddate, ad.fee, au.name as marketer, ADdesc.description ,c.name as companyname
			<cfloop query="QContacts2">
				,ui#Qcontacts2.contact_type_acronym#.lastname + ', ' + ui#Qcontacts2.contact_type_acronym#.firstname as #Qcontacts2.contact_type_acronym#contactname, #Qcontacts2.contact_type_acronym#.contact_id as #Qcontacts2.contact_type_acronym#contact_id
			</cfloop>
			FROM advertising as AD 
			LEFT JOIN admin_users as au on au.adminid = ad.marketer_id 
			LEFT JOIN advertising_components as Adc on Adc.advertising_id = ad.ID 
			LEFT JOIN advertising_descriptions as ADdesc on ADdesc.id = ADc.component_id 
			LEFT JOIN companies as c on c.id = AD.company_id 
			<cfloop query="Qcontacts2">
				left join client_services_contacts as #Qcontacts2.contact_type_acronym# on #Qcontacts2.contact_type_acronym#.contact_id = #arguments.indivnum# and #Qcontacts2.contact_type_acronym#.contacttypeid = #Qcontacts2.id# and #Qcontacts2.contact_type_acronym#.service_id = AD.ID 			left join client_services_contact_Types as #Qcontacts2.contact_type_acronym#types on #Qcontacts2.contact_type_acronym#types.id = #Qcontacts2.contact_type_acronym#.contacttypeid 
				left JOIN Users_Info AS ui#Qcontacts2.contact_type_acronym# ON ui#Qcontacts2.contact_type_acronym#.ID = #Qcontacts2.contact_type_acronym#.contact_id 
			</cfloop>
			WHERE 0=0 and (				 
				 <cfloop query="Qcontacts2">
					#Qcontacts2.contact_type_acronym#.contact_id = #arguments.indivnum# or
					</cfloop>
					0 = 1)
			ORDER BY ad.startdate DESC

Open in new window

0
 
lantervjAuthor Commented:
This is how the query that returns zero rows should looks when it resolves;

 
SELECT     TOP (100) PERCENT AD.id, AD.package_id, AD.startdate, AD.enddate, AD.fee, au.Name AS marketer, ADdesc.description, c.Name AS companyname, 
                      uihr.LastName + ', ' + uihr.FirstName AS hrcontactname, hr.contact_id AS hrcontact_id, uimkt.LastName + ', ' + uimkt.FirstName AS mktcontactname, 
                      mkt.contact_id AS mktcontact_id, uitax.LastName + ', ' + uitax.FirstName AS taxcontactname, tax.contact_id AS taxcontact_id, 
                      uiag.LastName + ', ' + uiag.FirstName AS agcontactname, ag.contact_id AS agcontact_id
FROM         dbo.Advertising AS AD LEFT OUTER JOIN
                      dbo.Admin_Users AS au ON au.adminID = AD.marketer_id LEFT OUTER JOIN
                      dbo.Advertising_Components AS Adc ON Adc.advertising_id = AD.id LEFT OUTER JOIN
                      dbo.Advertising_Descriptions AS ADdesc ON ADdesc.id = Adc.component_id LEFT OUTER JOIN
                      dbo.Companies AS c ON c.ID = AD.company_id LEFT OUTER JOIN
                      dbo.Client_Services_Contacts AS hr ON hr.contact_id = 27020 AND hr.ContactTypeID = hr.id AND hr.service_id = AD.id LEFT OUTER JOIN
                      dbo.Client_Services_Contact_Types AS hrtypes ON hrtypes.id = hr.ContactTypeID LEFT OUTER JOIN
                      dbo.Users_Info AS uihr ON uihr.ID = hr.contact_id LEFT OUTER JOIN
                      dbo.Client_Services_Contacts AS mkt ON mkt.contact_id = 27020 AND mkt.ContactTypeID = mkt.id AND mkt.service_id = AD.id LEFT OUTER JOIN
                      dbo.Client_Services_Contact_Types AS mkttypes ON mkttypes.id = mkt.ContactTypeID LEFT OUTER JOIN
                      dbo.Users_Info AS uimkt ON uimkt.ID = mkt.contact_id LEFT OUTER JOIN
                      dbo.Client_Services_Contacts AS tax ON tax.contact_id = 27020 AND tax.ContactTypeID = tax.id AND tax.service_id = AD.id LEFT OUTER JOIN
                      dbo.Client_Services_Contact_Types AS taxtypes ON taxtypes.id = tax.ContactTypeID LEFT OUTER JOIN
                      dbo.Users_Info AS uitax ON uitax.ID = tax.contact_id LEFT OUTER JOIN
                      dbo.Client_Services_Contacts AS ag ON ag.contact_id = 27020 AND ag.ContactTypeID = ag.id AND ag.service_id = AD.id LEFT OUTER JOIN
                      dbo.Client_Services_Contact_Types AS agtypes ON agtypes.id = ag.ContactTypeID LEFT OUTER JOIN
                      dbo.Users_Info AS uiag ON uiag.ID = ag.contact_id
WHERE     (0 = 0) AND (hr.contact_id = 27020) OR
                      (0 = 0) AND (mkt.contact_id = 27020) OR
                      (0 = 0) AND (tax.contact_id = 27020) OR
                      (0 = 0) AND (ag.contact_id = 27020) OR
                      (0 = 0) AND (0 = 1)
ORDER BY AD.startdate DESC

Open in new window

0
All Courses

From novice to tech pro — start learning today.