Обсуждение: conditional query?
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.
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
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>
As you say, it is easiest if there is only one level of 'parenting'..! SELECT p.id, CASE WHEN p.UseParentAddress = 'Y' THEN (SELECT address FROM profile WHERE id = p.parentid) ELSE address AS address FROM Profile p If you do have multiple levels, you'll need to recurse, either with a cursor or a while loop - it might make sense to builda temporary table of all profiles for which you need addresses, and then loop around it until they are all filled in(or similar). Cheers, Matt Mlunnon @ RWA <mlunnon@rwa-net.co.uk> wrote : > 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 --Matt