Hi Bob
> 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
what type is SpouseRowId?
If it's Owner_Agent, you can use the "->" syntax in your query.
This is a short inner join!
Select T1.RowId
T1.NAME,
T1.HOME_STATE,
T2.ABBREVIATION,
T1.SPOUSERowId->Name as SpouseRowIdName
T4.NAME AS SPOUSENAME,
T1.MAILING_STATE,
T3.ABBREVIATION
From
OWNER_AGENT T1
where
T1.CVM_DEV_SELECTED='Y'
HTH
Florian
