Hello. Any SQL Guru up for a challenge? I've inherited a wacky database that I need to pull info from. I'm trying to pull a range of prices for a specific product. I'd like to simply the two queries below (which work), possibly using sub-queries or some other SQL trick. Any ideas?
<cfset pid = 11444> <cfquery name="getOptions" datasource="#dsn#"> select optID from productOptions where productID = <cfqueryparam value="#pid#" cfsqltype="cf_sql_integer"> order by optID </cfquery> <cfset optIDList = valueList(getOptions.optid)> <cfquery name="q" datasource="#dsn#"> Select min(optprice) as minprice, max(optprice) as maxprice from options where optID in (<cfqueryparam value="#optIDList#" cfsqltype="cf_sql_integer" list="Yes">) </cfquery> <cfoutput> <cfif q.minprice eq q.maxprice>#dollarFormat(q.minprice)#<cfelse>#dollarFormat(q.minprice)# - #dollarFormat(q.maxprice)#</cfif> </cfoutput> Thanks, CV ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:329621 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
