You are here: Re: Compare record count in table1 to qty in table2? « MsSQL Server « IT news, forums, messages
Re: Compare record count in table1 to qty in table2?

Posted by rdraider on 10/13/06 18:17

Thanks, this worked well.


"Hugo Kornelis" <hugo@perFact.REMOVETHIS.info.INVALID> wrote in message
news:sltni21m6vu7ci713ekmqt72us0op6bjdo@4ax.com...
> 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
>

 

Navigation:

[Reply to this message]


Удаленная работа для программистов  •  Как заработать на Google AdSense  •  England, UK  •  статьи на английском  •  PHP MySQL CMS Apache Oscommerce  •  Online Business Knowledge Base  •  DVD MP3 AVI MP4 players codecs conversion help
Home  •  Search  •  Site Map  •  Set as Homepage  •  Add to Favourites

Copyright © 2005-2006 Powered by Custom PHP Programming

Сайт изготовлен в Студии Валентина Петручека
изготовление и поддержка веб-сайтов, разработка программного обеспечения, поисковая оптимизация