Kevin,

Of course you would not have posted it if not everyone expects a result of 1 record, so I ruled that one out inmediately :)

After 2 minutes, I saw there is an unfortunate typo: VendorPk ISO VendorFk in the subquery.
Without trying I guessed this would either return all rows or an error message as the field is not part of the table in the subquery and I was not sure where this would raise a correlating field error message or not.

After trying I saw it did return all rows. I guess this behavior is because of Microsoft implementation. It would not surprise me if it would fail in another SQL dialect.
This can be the source of bugs that are very hard to track down.

Walter,










>Suppose we have 2 tables....a simple vendor master and a simple orders table
>
>

>CREATE TABLE dbo.VendorMaster  (VendorPK int, VendorName varchar(100))
>CREATE TABLE dbo.OrderTable  (OrderID int, VendorFK int, OrderDate Date, OrderAmount money)
>
>INSERT INTO dbo.VendorMaster VALUES (1, 'Vendor A'), 
>                                                                     (2, 'Vendor B')
>
>INSERT INTO dbo.OrderTable VALUES (1, 1, '1-1-2008', 100), 
>                                                                  (2, 2, '1-1-2009', 50)
>

>
>Suppose I want the names of those Vendors with an order in 2008. Assume the inserts above worked fine, and I have 2 vendor rows and 2 order rows. Yes, I could do this with a JOIN. But I'll do it with a subquery. I write the following:
>
>

>SELECT * FROM dbo.VendorMaster   
>   WHERE VendorPK IN (SELECT VendorPK FROM dbo.OrderTable WHERE YEAR(OrderDate) = 2008)
>

>
>Will I get back...(choose only one answer)
>
>A) zero rows
>B) 1 row
>C) 2 rows
>D) 4 rows
>E) an error message
>
>Again, don't copy/paste the code and run it.....that would be cheating :)
>
>Is this a trick question? Give it a try!