SQL to Retrieve One Record from Several Possible Records
I am trying to select records based on a precedence. I'm using SQL2005. These records are address records for our students and employees. Each person will have, at least, one address, but many people will have more than one address. The addresses are coded so that we know the type of address: H = Home, LOC = Local, RMA = Room Assignment. I want to pull only one record based on a precedence. For example, if I find an RMA record, then I want to pull that and no other records. If there is no RMA record, I need to find a LOC record, if one exists. Repeat as needed until I find that last address type. The precedence is: 1 - RMA 2 - LOC 3 - H Here is the first step to find the address type in the order I need: SELECT DISTINCT TOP 1 pseason.id ,pseason.addr_type ,pseason.person_addresses ,CASE WHEN pseason.addr_type = 'H' THEN 1 WHEN pseason.addr_type = 'AM' THEN 2 WHEN pseason.addr_type = 'ISIR' THEN 3 ELSE 999 END AS sortOrder FROM PSeason WHERE pseason.id = '133' ORDER BY sortOrder Is there a better way to select these records? Thanks M!ke ~| ColdFusion MX7 by Adobe® Dyncamically transform webcontent into Adobe PDF with new ColdFusion MX7. Free Trial. http://www.adobe.com/products/coldfusion?sdid=RVJV Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:282493 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: SQL to Retrieve One Record from Several Possible Records
You could do multiple joins and check for null in your case statement. (this is assuming you have multiple addresses in a separate table with a primary key somehow relating to your main user database. In my example I am joining your pSeason file with the alias A to my theoretical pAddresses file with alias B against B.seasonID. Then I join it using a left join 2 more times. If a left join returns no record, it will return NULL when checking for that field.) Hope this helps and hope it makes sense! SELECT pseason.id ,pseason.addr_type ,pseason.person_addresses ,CASE WHEN A.addr_type IS NOT NULL THEN A.myAddressField WHEN A.addr_type IS NULL AND B.addr_type IS NOT NULL THEN B.myAddressField WHEN A.addr_type IS NULL AND B.addr_type IS NULL THEN C.myAddressField ELSE 'No Address Found' END AS SeasonAddress FROM Pseason A LEFT JOIN Paddresses B ON A.id = B.seasonID AND B.addr_type = 'H' LEFT JOIN Paddresses C ON A.id = C.seasonID AND C.addr_type = 'AM' LEFT JOIN Paddresses D ON A.id = D.seasonID AND D.addr_type = 'ISIR' WHERE pseason.id = '133' Chris Peterson Gainey IT Adobe Certified Advanced Coldfusion Developer --- From: Dawson, Michael Sent: Thursday, June 28, 2007 2:56 PM To: CF-Talk Subject: SQL to Retrieve One Record from Several Possible Records I am trying to select records based on a precedence. I'm using SQL2005. These records are address records for our students and employees. Each person will have, at least, one address, but many people will have more than one address. The addresses are coded so that we know the type of address: H = Home, LOC = Local, RMA = Room Assignment. I want to pull only one record based on a precedence. For example, if I find an RMA record, then I want to pull that and no other records. If there is no RMA record, I need to find a LOC record, if one exists. Repeat as needed until I find that last address type. The precedence is: 1 - RMA 2 - LOC 3 - H Here is the first step to find the address type in the order I need: SELECT DISTINCT TOP 1 pseason.id ,pseason.addr_type ,pseason.person_addresses ,CASE WHEN pseason.addr_type = 'H' THEN 1 WHEN pseason.addr_type = 'AM' THEN 2 WHEN pseason.addr_type = 'ISIR' THEN 3 ELSE 999 END AS sortOrder FROM PSeason WHERE pseason.id = '133' ORDER BY sortOrder Is there a better way to select these records? Thanks M!ke ~| Upgrade to Adobe ColdFusion MX7 The most significant release in over 10 years. Upgrade see new features. http://www.adobe.com/products/coldfusion?sdid=RVJR Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:282494 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: SQL to Retrieve One Record from Several Possible Records
Chris, thanks! This is an interesting solution. I assume that if I have more than three address types, then I will need a left join for each additional type? I assume I will also need to add another when to my case statement. At this point, I'm only interested in finding the first-found value in a list. The addresses are then linked with a PK/FK relation that will be easy to join. I have to go to a meeting, so I'll mess around with this when I get bored. Thanks -Original Message- From: Peterson, Chris [mailto:[EMAIL PROTECTED] Sent: Thursday, June 28, 2007 2:07 PM To: CF-Talk Subject: RE: SQL to Retrieve One Record from Several Possible Records You could do multiple joins and check for null in your case statement. (this is assuming you have multiple addresses in a separate table with a primary key somehow relating to your main user database. In my example I am joining your pSeason file with the alias A to my theoretical pAddresses file with alias B against B.seasonID. Then I join it using a left join 2 more times. If a left join returns no record, it will return NULL when checking for that field.) Hope this helps and hope it makes sense! SELECT pseason.id ,pseason.addr_type ,pseason.person_addresses ,CASE WHEN A.addr_type IS NOT NULL THEN A.myAddressField WHEN A.addr_type IS NULL AND B.addr_type IS NOT NULL THEN B.myAddressField WHEN A.addr_type IS NULL AND B.addr_type IS NULL THEN C.myAddressField ELSE 'No Address Found' END AS SeasonAddress FROM Pseason A LEFT JOIN Paddresses B ON A.id = B.seasonID AND B.addr_type = 'H' LEFT JOIN Paddresses C ON A.id = C.seasonID AND C.addr_type = 'AM' LEFT JOIN Paddresses D ON A.id = D.seasonID AND D.addr_type = 'ISIR' WHERE pseason.id = '133' Chris Peterson Gainey IT Adobe Certified Advanced Coldfusion Developer --- From: Dawson, Michael Sent: Thursday, June 28, 2007 2:56 PM To: CF-Talk Subject: SQL to Retrieve One Record from Several Possible Records I am trying to select records based on a precedence. I'm using SQL2005. These records are address records for our students and employees. Each person will have, at least, one address, but many people will have more than one address. The addresses are coded so that we know the type of address: H = Home, LOC = Local, RMA = Room Assignment. I want to pull only one record based on a precedence. For example, if I find an RMA record, then I want to pull that and no other records. If there is no RMA record, I need to find a LOC record, if one exists. Repeat as needed until I find that last address type. The precedence is: 1 - RMA 2 - LOC 3 - H Here is the first step to find the address type in the order I need: SELECT DISTINCT TOP 1 pseason.id ,pseason.addr_type ,pseason.person_addresses ,CASE WHEN pseason.addr_type = 'H' THEN 1 WHEN pseason.addr_type = 'AM' THEN 2 WHEN pseason.addr_type = 'ISIR' THEN 3 ELSE 999 END AS sortOrder FROM PSeason WHERE pseason.id = '133' ORDER BY sortOrder Is there a better way to select these records? Thanks M!ke ~| Create Web Applications With ColdFusion MX7 Flex 2. Build powerful, scalable RIAs. Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJS Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:282498 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: SQL to Retrieve One Record from Several Possible Records
,CASE WHEN A.addr_type IS NOT NULL THEN A.myAddressField WHEN A.addr_type IS NULL AND B.addr_type IS NOT NULL THEN B.myAddressField WHEN A.addr_type IS NULL AND B.addr_type IS NULL THEN C.myAddressField ELSE 'No Address Found' END AS SeasonAddress rather than these CASE statements, you could use COALESCE...the myAddressField should be NULL if the addr_type is NULL COALESCE(a.myAddressField,b.myAddressField,c.myAddressField,'No Address Found') AS SeasonAddress ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:282502 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
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 = '133' ORDER BY RoomTypes.OrderID /cfquery -- Alan Rother Adobe Certified Advanced ColdFusion MX 7 Developer Manager, Phoenix Cold Fusion User Group, AZCFUG.org ~| Upgrade to Adobe ColdFusion MX7 The most significant release in over 10 years. Upgrade see new features. http://www.adobe.com/products/coldfusion?sdid=RVJR Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:282509 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: SQL to Retrieve One Record from Several Possible Records
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 = '133' 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 = '133' 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
