Re: Simple(?) SQL problem
Douglas Wilson wrote:
>
> Douglas Wilson wrote:
> >
>
> > select job_id, job_desc, job_invoice,
> > (select sum(txn_lbr_val)
> > from transaction
> > where job_id=txn_job_id
> > and txn_aid=1000),
> > (select sum(txn_lbr_val)
> > where job_id=txn_job_id
> > and txn_aid=2000)
> > from job
> > where job_date > "12/1/1996"
> and job_id in
> (select distinct txn_job_id
> from transaction
> where txn_aid=1000 or txn_aid=2000)>
Sometimes I'm use new tricks too quickly, here's another
way that might work:
select job_id, job_desc, job_invoice,
sum(t1.txn_lbr_val), sum(t2.txn_lbr_val)
from job, outer transaction t1, outer transaction t2
where job_id=t1.txn_job_id
and t1.txn_aid=1000
and job_id=t2.txn_lbr_val
and t2.txn_aid=2000
and job_date > "12/1/1996"
group by 1,2,3
having sum(t1.txn_lbr_val)>0 or sum(t2.txn_lbr_val)>0
This is untested, I believe you need the outer joins if
one sum is 0 (due to no records) but the other is non-zero;
if there were no zero sums (due to no records) you could probably take
out the outer's and the 'having' clause.
*** Disclaimer: These are the opinions of the poster not Amgen Inc.***