CODEWITHSUNDEEP
sqloracle-databasequery-optimization
zapwreckage
zapwreckage

Reputation: 1

Oracle SQL - Combine two select statements

select qi.qtyonhand + qd.delqty as teste,
       qi.itemname
  from qitem qi, qdel qd
 where qi.itemname = qd.itemname
   and qd.deptname = 'recreation';


select qi.qtyonhand - qs.saleqty as teste,
       qi.itemname
  from qsale qs, qitem qi
 where qi.itemname = qs.itemname
   and qs.deptname = 'recreation';

I'm trying to update the qitem quantity count by adding from the qdel table's quantity and by subtracting from the qsale table's quantity. I'm trying to put it all into one column, however there may not be items in the qdel table with the department name of "recreation" so when I try to put the select statements into one, it leaves out some items from qitems.

Upvotes: 0

Views: 49

Answers (1)

Dave Costa
Dave Costa

Reputation: 48121

How is this? If this does not give the desired result, can you show some sample data to better explain what you need?

select qi.qtyonhand + NVL(qd.delqty,0) + NVL(qa.saleqty,0)  as teste, qi.itemname
from qitem qi
left outer join (select * from qdel where deptname = 'recreation') qd
  on qi.itemname = qd.itemname
left outer join (select * from qsale where deptname = 'recreation') qs
  on qi.itemname = qs.itemname
where (qd.delqty is not null or qs.saleqty is not null)

(There may be a simpler way to do it, especially if qitem also has a deptname column.)

Upvotes: 1

Related Questions