I have a query that works:
DECLARE @ProductID int
SET @ProductID = '1234'
SELECT DISTINCT TOP 12 a.ProductID
FROM A a
WHERE a.CategoryID IN (SELECT b.CategoryID FROM B b WHERE b.ProductID = @ProductID)
AND a.ProductID != @ProductID
It returns a list of 12 product numbers, all unique.
I need to store these results in a variable, comma separated, because that's what 3rd party stored procedure needs. So I have this:
DECLARE @ProductID int
DECLARE @relatedprods varchar(8000)
SET @ProductID = '1234'
SET @relatedprods = ''
SELECT TOP 12 @relatedprods = @relatedprods + CONVERT(VARCHAR(20), a.ProductID) + ', '
FROM A a
WHERE a.CategoryID IN (SELECT b.CategoryID FROM B b WHERE B.ProductID = @ProductID)
AND a.ProductID != @ProductID
SELECT @relatedprods
Now, none of these are distinct, but it is returning 12 rows.
Now I add the 'distinct' back in, like in the first query:
DECLARE @ProductID int
DECLARE @relatedprods varchar(8000)
SET @ProductID = '1234'
SET @relatedprods = ''
SELECT DISTINCT TOP 12 @relatedprods = @relatedprods + CONVERT(VARCHAR(20), a.ProductID) + ', '
FROM A a
WHERE a.CategoryID IN (SELECT b.CategoryID FROM B b WHERE B.ProductID = @ProductID)
AND a.ProductID != @ProductID
SELECT @relatedprods
Only one product is returned in the comma separated list! Does 'distinct' not work in assignment statements? What did I do wrong? Or is there a way to get around this?
Thanks in advance!
