Two SQL statements - Not Sure How to Proceed

[scripter73]

I originally posted this in the ColdFusion forum, but since its more of a SQL-related question - thought I'd post here.
------------


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

 

[SQLSister]

A join is generally more efficient. YOu could also consider using your second query as a derived table and joining to that, just make sure not to ask to return any more fields thatn you will actually need.

Questions about posting. See faq183-874

 

[scripter73]

Thanks for the response. How do I incorporate the query as a derived table? Would I already have to have this query performed? The 2nd query is dependent on my Customer ID from the 1st query.

I've been playing around with the joins, and I have all of the records I need. I just need to update the null values of the 1st query with the result of a 2nd query.

If you could show me a dummied-down version of what you mean, it would really help me out. You don't have to apply to my code exactly.

Thanks again for the advice.

scripter73


Change Your Thinking, Change Your Life.

 

[SQLSister]

select t.field1, t.field2, isnull(t.field4, a.field3) from table1 t
join (select isfield,field3 from table2 where field3>10) a
on t.idfield = a.idfield

Important things to remember. First when you use a derived table you must give it an alias (a in this example). You also must include the fields you need to join on.

As far as using the isnull function - it will replace one value with another when the first value is null. However both fields in it must be the same data type, so if they aren't in your two tables, you will need to convert or cast one to the other datatype.

Questions about posting. See faq183-874