Re: 2nd try

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
Received on Fri Nov 05 2004 - 15:28:45 CST