Re: Simple(?) SQL problem
Francis Fang <ffang@grover.printing.uiowa.edu> wrote in article
<32B63C45.6D3C@grover.printing.uiowa.edu>...
> Table Job Table Transaction
> job_id <=======> txn_job_id
> job_desc txn_lbr_val
> job_invoice txn_aid
> job_date
>
>
> What I need is an SQL statement that would retrieve:
>
> job_id, job_desc, job_invoice, sum(txn_lbr_val), sum(txn_lbr_val)
> ^ ^
> where txn_aid=1000 |
> where txn_aid=2000
>
> All of this where job_date > "12/1/96"
>
While there are solutions which involve temporary tables which may
sometimes
execute faster, here's a single SQL statement using correlated subqueries
which should do the trick:
select j.job_id, j.job_desc, j.job_invoice,
(select sum(txn_lbr_val)
from transaction t1
where t1.txn_job_id = j.job_id and t1.txn_aid = 1000) lbr_val1,
(select sum(txn_lbr_val)
from transaction t2
where t2.txn_job_id = j.job_id and t2.txn_aid = 2000) lbr_val2
from job j
where job_date > "12/1/96"
HTH
--
Irwin Goldstein
Objective Software Systems, Inc.
http://www.objectsoft.com