Try something like

SELECT p2.*
FROM profile p1, profile p2
WHERE ( p1.id =1 AND useParenAddres = 'N' AND p2.id = p1.id )
OR ( p1.id =1 AND useParenAddres = 'Y' AND p2.id = p1.parentId)

Obviously this won't work if you have more than one level of parent hood, i.e. it would pick up a grand parent. If this is the case then there is some kind of tree walking functionality in Postgres but I don't know how portable this is or whether it will solve your problem.

Another way to solve the grand parent thing would be to define a recursive function.

Happy coding.

Cheers
Matthew

Achilleus Mantzios wrote:

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