I'n not the original poster but that does look like the cleanest approach I've seen so far. I think you can even use that in a select column subquery to handle a query for multiple people (or whatever pseason.id represents)... select person.*, (SELECT TOP 1 pseason.person_addresses FROM PSeason INNER JOIN RoomTypes ON PSeason.addr_type = RoomTypes.addr_type WHERE PSeason.id = person.id ORDER BY RoomTypes.OrderID ) as best_person_addresses FROM person WHERE -- put any constraints on person here, or join person to anything else person.id = '0000133'
This looks nasty but SQL Server can a good do a decent job of optimizing this kind of query so it performs as good as a JOIN (but you want to check the query plan to make sure). Stick it in a view and you don't even have to look at it too often. The problem with it is you can only return one column from the subquery, but sometimes that's enough. - Mark ________________________________ From: Alan Rother [mailto:[EMAIL PROTECTED] Sent: Thu 6/28/2007 5:47 PM To: CF-Talk Subject: Re: SQL to Retrieve One Record from Several Possible Records Just to offer an alternate. Instead of those time consuming case statements and joins, you could just add a new table to assign a sort order value to each of the Types, such as you said the table would look like this 1 - RMA 2 - LOC 3 - H Where the numeric value would be the type's ordering ID Then you could do a simple query with a basic join off of this table, order by it and only take the top record. This would be the "simplest" approach to solving the problem, but only if you can add another table. <cfquery> SELECT DISTINCT TOP 1 pseason.id ,pseason.addr_type ,pseason.person_addresses FROM PSeason INNER JOIN RoomTypes ON PSeason.addr_type = RoomTypes.addr_type WHERE pseason.id = '0000133' ORDER BY RoomTypes.OrderID </cfquery> -- Alan Rother Adobe Certified Advanced ColdFusion MX 7 Developer Manager, Phoenix Cold Fusion User Group, AZCFUG.org ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| ColdFusion MX7 and Flex 2 Build sales & marketing dashboard RIAâs for your business. Upgrade now http://www.adobe.com/products/coldfusion/flex2?sdid=RVJT Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:282525 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
