Posted by Ed Murphy on 10/10/06 02:43

rdraider wrote:

> We have an inventory table (Items) that contains item_no and qty_on_hand
> fields.
> Another table (Item_Serial) contains serial numbers for any item that has
> serial numbers.
> If an item has 10 qty_on_hand, it should have 10 records in Item_Serial, one
> unique serial number for each item.
>
> I am trying to find items where the number of serial numbers does not equal
> the qty_on_hand.
> Here is a query I'm trying to use and, of course, it does not work:
>
> select Items.item_no, Items.qty_on_hand
> from Items inner join Item_Serial on Item_Serial.item_no = Items.item_no
> where Items.qty_on_hand <> count(Item_Serial.item_no)

I believe this will work:

select Items.item_no, Items.qty_on_hand,
coalesce(sum(Item_Serial.item_no),0) serial_qty_on_hand
from Items
left join Item_Serial on Item_Serial.item_no = Items.item_no
where Items.qty_on_hand <> coalesce(sum(Item_Serial.item_no),0)

• Next in forum: Re: how to delete lines from text file
• Prev in forum: Re: Date overlapping
• Thread view: Re: Compare record count in table1 to qty in table2?

[Reply to this message]