Re: 2nd try

From: Bricklen <bricklen_at_zyahoo.zcomz>
Date: Fri, 05 Nov 2004 21:39:32 GMT
Message-ID: <oqSid.83530$df2.14069@edtnps89>

Patrice Béliveau wrote:
> On Fri, 05 Nov 2004 21:06:54 GMT, Bricklen <bricklen_at_zyahoo.zcomz>
> wrote:
>
>

>>Patrice Béliveau wrote:
>>
>>>Hi,
>>>
>>>I have two table, one is containing a dated list of delivery to do and
>>>the second contain a list of delivery done.
>>>
>>>ex: 	Table 1  --> 	Order No,
>>>			Delivery date requested, 
>>>			Quantity requested
>>>
>>>	Table 2 -->	Shipping No.
>>>			Order No.
>>>			Date shipped
>>>			Quantity shipped
>>>	
>>>
>>>They are not into the same list because there is no one on one
>>>relation between those two list (ex: some delivery might have been
>>>back order, or that might have been some over shipment, etc...)
>>
>>So "Order No" in each table is _not_ related? As in PK/FK relationship?

>
>
> Yes they are. What I mean is that the fifth requested delivery is not
> nescessary the fifth one done, because for some reason, more than one
> delivery might have been required to fulfill one requested delivery
> _or_ one delivery made may fulfill two requested delivery
>
> Table 1 is what the customer want and table 2 is what was actually
> done
>
> So what I want to know is, in my list of delivery requested (for a
> specific order) where am I, what is left to delivery
>

>>>Another way of looking at it would be:
>>>
>>>- Give me all Order No, where SUM(quantity requested) > SUM(quantity
>>>shipped).
>>>
>>>but then sum on the left side of a test is not permitted
>>
>>maybe not in the WHERE clause, but you could do it with a GROUP BY and a 
>>HAVING clause

>
>

Obviously I'm not having a clever-moment day, because all I can see is something like this:

select * from tab_1 where ord_num not in (select ord_num from tab_2) order by date_requested;

This assumes that an order that is in table 2 has been completed. Perhaps someone else will chime in and supply the correct answer that I am obviously failing to see (or maybe I'll have an epiphany and understand the question better) :) Received on Fri Nov 05 2004 - 15:39:32 CST