Should I Update My Query?

[scripter73]

I have a query result set that was created with several joins. Here’s the abbreviated version:

<cfquery name="CustData" datasource="#request.dsn#">
SELECT
	….field list values here
		
FROM Customer pc
		left join area a on pc.area = a.areaid	
		LEFT JOIN layout l ON pc.DefaultInvoiceLayID = l.LayID
		LEFT JOIN users u ON pc.salesrep = u.userid
		LEFT JOIN paygroups ON pc.paygroup = paygroups.paygroupid
		<cfif IncludeContactsYN>
		LEFT JOIN contacts_Cust cc ON pc.recordid = cc.CustID
		</cfif> 
	 
WHERE
	pc.area IN (#area#)
	<cfif status neq "">
		<cfset QStatList = "">
		<!--- Make form list single quoted --->
		<cfloop index="StatDex" list="#status#">
			<cfset QStatList = listappend(QStatList, "'#StatDex#'")>
		</cfloop>
		and status in (#preservesinglequotes(QStatList)#)
	</cfif>
	<cfif Custtype neq "">
		and Custtype = '#Custtype#'
	</cfif>
	<cfif ShowNoBillDateOnly_YN is "yes">
		and IsDate(pc.LastBillDate) = 0
	</cfif>
	
ORDER BY
	<cfif sort eq "name">
		pc.Custname
	<cfelseif sort eq "area">
		r.areaname, pc.Custname
	<cfelseif sort eq "state">
		pc.state, pc.city, pc.Custname
	</cfif>
</cfquery>

There’s one column in the query above (PayGroupName) that has some null values that I need populated. Its because the Customers data table isn't updated with the latest Pay Group IDs (although they are in another table). Now, I have another Query “DefaultPayGroup” that can give me the values I need.

<cfquery name="DefaultPaygroup" datasource="#request.dsn#">
	SELECT     
		dbo.paygroups.paygroupid, dbo.paygroups_cust.CustPaygroupID, dbo.paygroups_cust.CustID, 
		dbo.paygroups_cust.IsDefaultYN, dbo.paygroups_cust.EffectiveDateStart, dbo.paygroups_cust.EffectiveDateEnd, 
		dbo.paygroups_cust.EffectiveNote, dbo.paygroups_cust.DTC, 
		dbo.paygroups_cust.DTM, dbo.paygroups_cust.UserC, dbo.paygroups_cust.UserM, dbo.paygroups_cust.isCustDefaultYN,
		dbo.paygroups.paygroupname, 
		dbo.paygroups_cust.ordertypeID, 1 AS sortby
	FROM         
		dbo.paygroups_cust RIGHT OUTER JOIN
		dbo.paygroups ON dbo.paygroups_cust.PaygroupID = dbo.paygroups.paygroupid
	WHERE
		(dbo.paygroups_cust.CustID = #CustID#) 
			and (dbo.paygroups_cust.isCustDefaultYN = 'yes')
</cfquery>

What’s the best way for me to approach this problem? Should I go back and update my CustData query with a query of a query? That sounds inefficient, or should I try yet another join?

Any advice you can provide is greatly appreciated.

Thanks in advance,

Scripter73



Change Your Thinking, Change Your Life.

 

[webmigit]

You might get a really good answer out of forum183 (SQL Server). If you use a different version of SQL just search for it.. I'm sure Tek Tips has a forum for it.

CF is easy to read and you'll probably find some cf'ers over there. Or you might clean most of the cf output it..

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.