Re: [ACFUG Discuss] Tricky SQL help?

[Frank Moorman]

First. let me say that MySql does allow for the COALESCE function and and Nelson's SQL should work. I would say however, the 3rd null parameter is not needed, if the first field is null it will use the second field whether or not it has a null value. SELECT  S.* , coalesce(A1.addresstype, A2.addresstype) AS AddressType , coalesce(A1.addresstype, A2.addresstype) AS AddressLine1 , coalesce(A1.addresstype, A2.addresstype) AS City , coalesce(A1.addresstype, A2.addresstype) AS State FROM Students S LEFT JOIN Addresses A1 ON S.id = A1.empid and A1.addressType = 'temporary forward' LEFT JOIN Addresses A2 ON S.id = A2.empid and A2.addressType = 'permanent forward' Second for performance, I agree with the general consensus that having the DB do all the work is usually the most efficient. There is one caveat for this though... datatype conversions.  Have coldfusion convert all datatypes to what is defined in the table definition. If for some strange reason, zipcodes are stored as an integer field, use WHERE zip_code = 7003, not WHERE zip_code = '07003'. The reverse is true too... if you have a field that stores numbers as a string use WHERE string_field = '34'; do not use WHERE string_field = 34; I used to work on an oracle system where people were ignorant of how a field was defined and oracle would slow to a crawl due to implicit type conversions. Remember, Coldfusion only needs to convert the datatype once, the database may do it for each individual row. On 04/09/2012 10:59 AM, Nelson Winters wrote: If you're using SQL Server, something like this should work: SELECT  S.* , coalesce(A1.addresstype, A2.addresstype, null) AddressType , coalesce(A1.addresstype, A2.addresstype, null) AddressLine1 , coalesce(A1.addresstype, A2.addresstype, null) City , coalesce(A1.addresstype, A2.addresstype, null) State FROM Students S  LEFT JOIN Addresses A1 ON S.id = A1.empid and A1.addressType = 'temporary forward' LEFT JOIN Addresses A2 ON S.id = A2.empid and A2.addressType = 'permanent forward' On Mon, Apr 9, 2012 at 10:45 AM, Tepfer, Seth <la...@emory.edu> wrote: I know this is not strictly a CF issue, but I’m trying to find the most efficient way of doing this, and a single query seems faster than 2 queries plus a query of queries.     I have two tables, one of student names, one of addresses.   Students: id, name, emplID Addresses: emplID, addressType, addressline1, city, state,   Student table data: 1, john dooley, 1234567 2, jane dooley, 2345678 3, tom dooley, 33456780   Addresses table data: 1234567, onCampus, 101 Dowman, null, null 1234567, home address, 100 main street, springfield, IL 1234567, permanent forward, 120 main street, Atlanta, GA 2345678, home address, 200 main street, springfield, MO 2345678, temporary forward, 130 main street, Atlanta, GA 3456789, home address, 300 main street, springfield, GA   Every student has a home address. Some have permanent or temporary forwards. The query results I want to return are ·         All the student names ·         If they have permanent or temporary forward, that address ·         If they do not, a null for the address (NOT the home address or onCampus address)   I’ve tried an outer join with a ‘where addressType = ‘permanent or temporary’, but that doesn’t give me all the names, just the ones with a forward. Thanks for any time you can give.   This e-mail message (including any attachments) is for the sole use of the intended recipient(s) and may contain confidential and privileged information. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this message (including any attachments) is strictly prohibited. If you have received this message in error, please contact the sender by reply e-mail message and destroy all copies of the original message (including attachments). ------------------------------------------------------------- To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink ------------------------------------------------------------- ------------------------------------------------------------- To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by http://www.fusionlink.com -------------------------------------------------------------