Re: 2nd try

From: DA Morgan <damorgan_at_x.washington.edu>
Date: Fri, 05 Nov 2004 18:30:05 -0800
Message-ID: <1099708135.781375@yasure>

Bricklen wrote:

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

All I can see is a student asking you to do their homework for them.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)

Received on Fri Nov 05 2004 - 20:30:05 CST