title: Can SQL Sub-query return two/more values but still compare against one of them?
body: <p>I have this query:</p>
  
  <pre><code>SELECT Items.Name, tblBooks.AuthorLastName, tblBooks.AuthorFirstName
  FROM Items WHERE Items.ProductCode IN (
  SELECT TOP 10 Recommended.ProductCode
  FROM 
  Recommended 
  INNER JOIN Stock ON Recomended.ProductCode = Stock.ProductCode
  AND Stock.StatusCode = 1
  WHERE (Recommended.Type = 'TOPICAL') ORDER BY CHECKSUM(NEWID()));
  </code></pre>
  
  <p>It is fine for my data, except that the Recommended table has a SKU field I need also however I cannot put it next to Recommended.ProductCode and have the query still work.<br>
  I have used JOINS for this query and these  work - but this query runs faster I just need the ProductCode and SKU from the Recommended table - how can this be done without needing yet another sub query?<br>
  Database: MS SQL Server 2000</p>