Can SQL Sub-query return two/more values but still compare against one of them?

Question

I have this query:

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()));

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.
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?
Database: MS SQL Server 2000

score 1 · Accepted Answer · answered May 15 '09 at 11:00

The subquery seems to be picking 10 random recommendations. I think you can do that without a subquery:

SELECT TOP 10
    Items.*,
    Recommended.*,
    Stock.*
FROM Items 
INNER JOIN Recommended 
    ON Items.ProductCode = Recommended.ProductCode
    AND Recommended.Type = 'TOPICAL'
INNER JOIN Stock 
    ON Recomended.ProductCode = Stock.ProductCode
    AND Stock.StatusCode = 1
ORDER BY CHECKSUM(NEWID())

This gives you access to all columns, without having to pass them up from the subquery.

score 0 · Answer 2 · answered May 15 '09 at 10:54

You can only return one value with the subselect, so you have to obtain the fields from the Recommended table by a join - which I presume is what you have already:

SELECT Items.Name, tblBooks.AuthorLastName, tblBooks.AuthorFirstName, Recommended.SKU
FROM Items 
INNER JOIN Recommended ON Recommended.ProductCode = Items.ProductCode
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()));

Most likely the Join in reality is an outer too I guess. This really shouldn't have any performance issues so long as you have both the Items and and Recommended tables indexed on ProductCode.

score 0 · Answer 3 · answered May 15 '09 at 10:58

I think you need to move the subquery out of the where clause:

SELECT Items.Name, tblBooks.AuthorLastName, tblBooks.AuthorFirstName, R.SKU
FROM Items 
INNER JOIN 
   (SELECT TOP 10 Recommended.ProductCode, Recommended.SKU FROM Recommended 
   INNER JOIN Stock ON Recommended.ProductCode = Stock.ProductCode AND 
   Stock.StatusCode = 1 WHERE (Recommended.Type = 'TOPICAL') 
   ORDER BY CHECKSUM(NEWID())) 
AS Rec ON Items.ProductCode = Rec.ProductCode;

The above is valid syntax in MySQL, your mileage may vary...

score 0 · Answer 4 · answered May 15 '09 at 11:00

Under those circumstances I would normally use an inner join to get the row filtering from the where clause I needed and the extra columns. Something like below; if this is what you did that gave you a performance hit then you might need to flip the query; go from recommended and join to items; as that will probably lead to more data filtering before the join.

SELECT Items.Name, tblBooks.AuthorLastName, tblBooks.AuthorFirstName
FROM Items 
Inner Join
(
SELECT TOP 10 Recommended.ProductCode, SKUID
FROM 
Recommended 
INNER JOIN Stock ON Recomended.ProductCode = Stock.ProductCode
AND Stock.StatusCode = 1
WHERE (Recommended.Type = 'TOPICAL')
) reccomended
on items.productcode - reccomended.ProductCode


ORDER BY CHECKSUM(NEWID()

Can SQL Sub-query return two/more values but still compare against one of them?

4 Answers4