Posted by Hugo Kornelis on 10/10/06 19:40

On Tue, 10 Oct 2006 01:08:33 GMT, 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)
>
>Anybody know how to do this?
>Thanks.
>

Hi rdraider,

You were nearly there :-)

SELECT Items.item_no, Items.qty_on_hand, COUNT(Item_Serial.item_no)
FROM Items
INNER JOIN Item_Serial
ON Item_Serial.item_no = Items.item_no
GROUP BY Items.item_no, Items.qty_on_hand
HAVING Items.qty_on_hand <> COUNT(Item_Serial.item_no);

(Untested - see www.aspfaq.com/50006 if you prefer a tested reply)

--
Hugo Kornelis, SQL Server MVP

• Next in forum: Re: how to delete lines from text file
• Prev in forum: SQL 2000 - Initiate or Force Replication
• Thread view: Re: Compare record count in table1 to qty in table2?

[Reply to this message]