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 <[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 <http://www.fusionlink.com> > -------------------------------------------------------------
