Re: Query problem

Alexander Medvedev wrote:

> JAMES wrote:
>
> > I have 3 tables, I want to show current stock quantity for each item.
> >
> > Table item
> > <item_id> <item_name> <cost>
> > <selling_price>
> > P001 A4 Paper 10 12
> > P002 Pen 2
> > 3
> > P003 Eraser 1 2
> > P004 Book 20 25
> >
> > Table stock_out
> > <item_id> <qty> <date>
> > P001 4 11-NOV-00
> > P002 2 02-NOV-00
> >
> > Table stock_in
> > <item_id> <qty> <date>
> > P001 10 01-OCT-00
> > P002 10 01-AUG-00
> > P003 10 01-JUL-00
> > P004 10 01-AUG-00
> >
> > How can I show the following result?
> > <item_id> <qty>
> > P001 6
> > P002 8
> > P003 10
> > P004 10
> >
> > THANKS
> > James
>
> Hi James, try this
>
> select item_id, si.qty-nvl(so.qty,0)
> from stock_in si, stock_out so
> where si.item_id=(+)so.item_id
>
> Regards Alexander Medvedev

A little addition. This select will work propertly if you have unique representativeness of each item_id in stock_in and stock_out tables (as explained in your example). Really (in common case) this condition may be violated. In this case you can use another query:

select item_id,sum(qty) from
(select item_id, sum(qty) qty from stock_in group by item_id union all
select item_id,sum(-qty) qty from stock_out group by item_id) group by item_id

Regards Alexander Medvedev Received on Thu Dec 07 2000 - 14:42:44 CET