[SQL] conditional query?

[Frank Morton]

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

Re: [SQL] conditional query?

[Achilleus Mantzios]

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


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

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


  
  
  

[SQL] selecting problems

[Shaun Watts]

What I am trying to do is select all the rows out of the categories
table (see below) and select the sum of j_amount out of the judgment
table.  Right now it is only returning the categories that are in the
judgment table.  I want it to return those fields with the amounts out
of the judgment table, but also the all the other categories from the
categories table with 0 as there amount.  Does anyone have a
suggestion.

select ca_code,ca_desc,sum(j_amount) as sum_amt,ca_dis_cycle
from categories LEFT OUTER JOIN judgment ON
(j_category=ca_code)
where j_case_no='45698'
and j_party_no=1
group by ca_code,ca_desc,ca_dis_cycle

Thanks,
Shaun
- 
Shaun,  
Phone 1-317-913-4160 Fax 1-317-913-4175 
CSI - Computer Systems, Inc. 
"Dictionary is the only place that success comes before work. Hard work is the price 
we 
must pay for success. I think you can accomplish anything if you're willing to pay the 
price."
Vince Lombardi 



---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] selecting problems

[Tomasz Myrta]

Dnia 2003-10-31 20:19, Użytkownik Shaun Watts napisał:
What I am trying to do is select all the rows out of the categories
table (see below) and select the sum of j_amount out of the judgment
table.  Right now it is only returning the categories that are in the
judgment table.  I want it to return those fields with the amounts out
of the judgment table, but also the all the other categories from the
categories table with 0 as there amount.  Does anyone have a
suggestion.
select ca_code,ca_desc,sum(j_amount) as sum_amt,ca_dis_cycle
from categories LEFT OUTER JOIN judgment ON
(j_category=ca_code)
where j_case_no='45698'
and j_party_no=1
group by ca_code,ca_desc,ca_dis_cycle
Thanks,
Shaun
This left join won't work. Where clause changes your left join into 
inner one.

select ca_code,ca_desc,sum(j_amount) as sum_amt,ca_dis_cycle
from categories LEFT OUTER JOIN judgment ON
 (j_category=ca_code and j_case_no='45698' and j_party_no=1)
group by ca_code,ca_desc,ca_dis_cycle
or

select ca_code,ca_desc,sum(j_amount) as sum_amt,ca_dis_cycle
from categories LEFT OUTER JOIN judgment ON (j_category=ca_code)
where (j_case_no='45698' and j_party_no=1) or j_category is null
group by ca_code,ca_desc,ca_dis_cycle
Probably you also need to change your sum(j_amount) into:
sum(case when j_amount is null then 0 else j_amount end)
Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
 subscribe-nomail command to [EMAIL PROTECTED] so that your
 message can get through to the mailing list cleanly

Re: 7.4 and 7.3.5 showstopper (was: Re: [SQL] Bug in Rule+Foreing

[Michele Bendazzoli]

On Thu, 2003-10-30 at 20:13, Jan Wieck wrote:
> Thanks for reporting, Michele. 

Thank to you! The speed and level of your responses exceeds every my
more rose-colored expectation ;-)

> In the meantime, you might want to use a 
> BEFORE INSERT trigger in PL/pgSQL that tries to UPDATE the row and if 
> GET DIAGNOSTICS tells it it succeeded, returns NULL to suppress the 
> INSERT. That should work around the bug for the time being.

It is not a problem for me, the db is not still in production, so I
think i wait for the patch.

Thank to all you again.

ciao, Michele


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings