I'm having trouble getting the appropriate SQL self-join stmt or a subquery stmt to select the appropriate record from the same file.
I have a table (Owner_Agent) that has a layout like this: RowId Name StreetAddress City State Zipcode Phone SpouseRowId (pointer to entry in same table) cvm_dev_selected (flag set to null or Y) I need to select all Owner records that are flagged with DevSelected='Y', then lookup the spoue's name (if there is a spouse). So the desired result set would look similar to: RowId Name SpouseRowId SpouseName I've tried the following SQL stmts: Select T1.RowId, T1.NAME, T1.HOME_STATE, T2.ABBREVIATION, T1.SPOUSE, T4.NAME AS SPOUSENAME, T1.MAILING_STATE, T3.ABBREVIATION From OWNER_AGENT T1 LEFT OUTER JOIN STATE T2 ON T2.NUMBER=T1.HOME_STATE, OWNER_AGENT LEFT OUTER JOIN STATE T3 ON T3.NUMBER=T1.MAILING_STATE, OWNER_AGENT T4 where T1.CVM_DEV_SELECTED='Y' and (T4.ROWID=IFNULL(T1.SPOUSE,'',T1.SPOUSE)) This statement will get the resultset I want with the exception of any Owners that don't have a spouse. Any suggestions for a way to retrieve ALL owners even ones that don't have a spouse? Thanks, Bob --------------------------------------------------------------- Robert H. Dobbins IT Administrator III Computer Operations University of TN College of Veterinary Medicine rhd @ [EMAIL PROTECTED] utk.edu ---------------------------------------------------------------
