Why dont you try a combination of
CASE WHEN ... THEN ... ELSE ... END construct
along with a LEFT OUTER join (in case parentId is null).
Not sure how "portable" the above will be.
O kyrios Frank Morton egrapse stis Oct 31, 2003 :
> I have a table called a "profile" that has company addresses as well
> as individual contact information. Simpifying:
>
> id (the primary key)
> parentId (any profile can have a parent ie, contact parent is a company)
> address (for this example, will just have one piece of the address)
> useParentAddress
>
> If "useParentAddress"=="Y", that means that the parent address of this
> person should really be used for mailings. If == "N" then the address
> with that profile is the right one to use.
>
> Is there any way to do a single select to get a single "address" back
> that is the right one depending on the value of "useParentAddress"
> field?
>
> Also want to make this sql as portable as possible.
>
> Will appreciate any ideas.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>
--
-Achilleus