And I'll disagree - make the DB do the work unless it is an extremely small dataset.
We don't know if he's working with a few hundred records or 20,000. While it won't matter if it's a small dataset, developers should learn how to get the data they need from the database so that in the even they ARE processing 100k records, it's done properly. I've seen even small apps/databases CRAWL by looping over data or running multiple queries when the developer didn't know how to properly query a limited dataset or group data using CFOUTPUT. -- Dawn On Mon, Apr 9, 2012 at 11:23 AM, <[email protected]> wrote: > Seth > I must agree with Charlie on this. > Even though the script Nelson provided will work I believe it is overkill > for your needs as described. > Join the two tables on empid for all records and simply do a conditional > output of the query. > Test if addressType is equal to temporary or permanent and output the > address fields. > If not then output null fields > Display or hide the address type as you wish > > IMHO > > > On , "Tepfer, Seth" <[email protected]> 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 > > > > > > ------------------------------------------------------------- > > > > > > >
