Re: SQL Query

dti wrote:
> Now that I have a flat spot on my head,(from beating my head against
> the monitor) I will post my question.
>
> The company I am working with has a database that they can not
> control. The software they use to interface with oracle (7.*.*)
> requires that some "things" remain the way they are. No views, no
> additional tables, no database links, etc. etc.
>
> Here's my problem:
>
> I want to know what items on order have either no inventory or the
> inventory available is not enough. The inventory table has a feature
> of storing each item with the lot code as a seperate column. Here is
> some sample data.
>
> TABLE INVT TABLE ORD
> LOT_CODE ITEM QTY ITEM ORD QTY
> ======================== =========================
> LOT1 ITEM1 0 ITEM1 12345 5
> LOT2 ITEM1 10 ITEM1 98765 5
> LOT3 ITEM1 5 ITEM2 12345 5
> LOT1 ITEM2 5 ITEM2 98765 5
> LOT5 ITEM2 3
> LOT4 ITEM2 0
>
>
> As you can see if I total the INVT for ITEM1 = 15 and ITEM2 = 8.
> If I total the ORD for ITEM1 = 10 and ITEM2 = 10.
> Then subtracting the difference I should see ITEM1 +5 ITEM2 -2.
>
> But with sql when I run a query of
> select ord.item, sum(ord.qty), sum(invt.qty)
> ,(sum(ord.qty)-sum(invt.qty))
> from ord, invt
> group by ord.item
>
> my results are
> ITEM ORD.QTY INVT.QTY DIFFERENCE
> ITEM1 10 30 20
> ITEM2 10 16 6
>
> You see my dilema. Anyone have a suggestion?

how's changing the last lines in:
from ord, invt
where ord.item = invt.item
group by ord.item

-- 
Regards, Frank van Bortel