Solved

dynamic sql

Posted on 2011-03-01
15
311 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:lantervj
  • 9
  • 6
15 Comments
 
LVL 39

Accepted Solution

by:
gdemaria earned 500 total points
Comment Utility
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
 

Author Comment

by:lantervj
Comment Utility
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
 
LVL 39

Expert Comment

by:gdemaria
Comment Utility

What table joins ADVERTISING and CONTACTS ?

0
 
LVL 39

Expert Comment

by:gdemaria
Comment Utility

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
 

Author Comment

by:lantervj
Comment Utility
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
 
LVL 39

Expert Comment

by:gdemaria
Comment Utility
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
 

Author Comment

by:lantervj
Comment Utility
I tried that and got all the ad records in the table.  I was curious about that too.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 39

Expert Comment

by:gdemaria
Comment Utility

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
 

Author Comment

by:lantervj
Comment Utility
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
 

Author Comment

by:lantervj
Comment Utility
The only things that works is to have the compare for the indivnum in both the left join and where clause.
0
 
LVL 39

Expert Comment

by:gdemaria
Comment Utility
it must be the other conditions of the join..

 it was worth a test I guess
0
 

Author Closing Comment

by:lantervj
Comment Utility
Once again, many thanks.
0
 

Author Comment

by:lantervj
Comment Utility
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
 

Author Comment

by:lantervj
Comment Utility
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
 

Author Comment

by:lantervj
Comment Utility
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

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Hi. There are several upload tutorials using jquery and coldfusion. I found a very interesting one here Upload Your Files using Jquery & ColdFusion and Preview them (http://www.randhawaworld.com/) . I did keep the main js functions but made sever…
Sometimes databases have MILLIONS of records and we need a way to quickly query that table to return the results me need. Sure you could use CFQUERY but it takes too long when there are millions of records. That is why SOLR was invented. Please …
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now