Re: [SQL] conditional query?

[mlunnon @ RWA]

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