|
|
Posted by rdraider on 05/07/07 22:09
Thanks for your help. A very good lesson my a newbie like me.
"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns9929F1A74E76FYazorman@127.0.0.1...
> rdraider (rdraider@sbcglobal.net) writes:
>> Thanks for the help. Can I ask a follow up?
>> What if you wanted to add the CategoryID to this so that the results
>> showed UnitsInStock < ReorderLevel and included items where the supplier
>> AND CategoryID were the same?
>> Examples: SupplierID 7 has 5 items returned but only 1 is below reorder
>> and all are different categories
>> SupplierID 23 has 3 items returned but only 2 share the same
>> CategoryID.
>
> This is precisely why I prefer EXISTS over IN. IN can only handle when
> the condition is on a single column. EXISTS is extensible:
>
> Select a.ProductID, a.ProductName, a.SupplierID, a.CategoryID,
> a.UnitsInStock, a.ReorderLevel
> from Products a
> where exists (
> SELECT *
> FROM Products b
> WHERE a.SupplierID = b.SupplierID
> AND a.CategoryID = b.CategoryID
> AND b.UnitsInStock < b.ReorderLevel
> )
> ORDER BY a.SupplierID, a.CategoryID, a.ProductID
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|