Have Geek Brain

Can SQL Sub-query return two or more values, but compare them with one of them?

I have this request:

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

This is fine for my data, except that there is a SKU field in the Recommended table that I need, but I can't put it next to Recommended.ProductCode and run the query.
I used JOINS for this request and this works - but this request is faster. I just need the ProductCode and SKU from the Recommended table - how can this be done without the need for another subquery?
Database: MS SQL Server 2000

0


a source to share


4 answers


The subquery seems to be picking 10 random recommendations. I think you can do it 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 from a subquery.

+1


a source


You can only return one value using a subplot, so you need to get the fields from the Recommended table by concatenating - I suppose this is what you already have:

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


Chances are the entry into reality is external, I think. This really shouldn't have a performance issue if you have both the Items and Recommended tables indexed in ProductCode.

0


a source


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 syntax in MySQL, your mileage may vary ...

0


a source


In these circumstances, I would normally use an inner join to get the filtering of rows from the where clause that I need and additional columns. Something like below; if this is what you did that gave you a performance hit, you may need to reverse the query; go from recommended and join subjects; as this will likely result in more filtering of the data before connecting.

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


a source






More articles:


All Articles