Oracle SQL - Combine two select statements

Question

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.

I don't see any update there, so I'm not really sure what you're trying to do. It seems to me like you might be looking to do an outer join. Btw why does everything start with q? — vesan
– vesan, Commented Sep 29, 2015 at 22:56
Like the first select statement has 8 rows of output, meanwhile the second select statement only has 4 rows, and only 2 rows were in both statements. Meaning I want the rows combined but no duplicate rows with the same item name. — zapwreckage
– zapwreckage, Commented Sep 29, 2015 at 23:20

Dave Costa · Accepted Answer · 2015-09-29 23:27:30Z

1

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.)

answered Sep 29, 2015 at 23:27

Dave Costa

48.3k8 gold badges61 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle SQL - Combine two select statements

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related