Try something like<br /><br /> SELECT p2.* <br /> FROM profile p1, profile p2 <br /> WHERE ( p1.id =1 AND
useParenAddres= 'N' AND p2.id = p1.id ) <br /> OR ( p1.id =1 AND useParenAddres = 'Y' AND p2.id = p1.parentId) <br
/><br/> 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
portablethis is or whether it will solve your problem.<br /><br /> Another way to solve the grand parent thing would be
todefine a recursive function.<br /><br /> Happy coding.<br /><br /> Cheers<br /> Matthew<br /><br /><br /> Achilleus
Mantzioswrote:<br /><blockquote cite="midPine.LNX.4.44.0310311544270.13725-100000@matrix.gatewaynet.com"
type="cite"><prewrap="">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 :
</pre><blockquote type="cite"><pre wrap="">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
</pre></blockquote><pre wrap=""> </pre></blockquote>
