sql server 2005 query - random child rows of UNIQUE random parent rows

Question

I got a parent table 'ProductCategory' and a child table 'Product'. I have this query that returns 3 random products:

SELECT    TOP (3) ProductId
FROM      Product
ORDER BY NEWID();

I want to enhance that query to achieve that all the products are from different product categories. So the query to get unique categories would be:

SELECT    TOP (3) ProductCategoryId
FROM      ProductCategory
ORDER BY NEWID();

I am not able to figure out how to combine those 2 queries to achieve my goal. The obvious query

SELECT    TOP (3) p.ProductId
FROM      Product p
where p.productcategory_ProductCategoryId in
    (
    SELECT    TOP (3) ProductCategoryId pc
    FROM      ProductCategory pc
    ORDER BY NEWID()
    )
ORDER BY NEWID();

does not work. It seems like the inner select statement is ignored. I also tried with the EXISTS statement or joining the tables. All with the same result.

Does someone have an idea? Thanks a lot in advance!

Answer 1

How about something like this? I was able to accomplish this task using a temp table and a cursor. Longer of a step, but it works.

create table #temp(
 productID int
,CategoryID int
)
declare @CategoryID int
declare ID_Cursor cursor
for select ProductCategoryID from ProductCategory order by NEWID() 
open ID_Cursor
FETCH NEXT FROM ID_Cursor INTO @CategoryID

WHILE @@FETCH_STATUS = 0 and (select COUNT(*) from #temp)<3
BEGIN

if (@CategoryID not in (select CategoryID from #temp))
Begin
insert into #temp
SELECT top(1) ProductID, @CategoryID
  FROM [Product] 
  order by NEWID() 
 END 

FETCH NEXT FROM ID_Cursor INTO @CategoryID
END 
CLOSE ID_Cursor
DEALLOCATE ID_Cursor

select * from #temp
drop table #temp

Answer 2

You have to decouple the 2 queries and this is one solution

Per ProductCategoryId, correlated subquery to get a random product. Uniqueness of ProductCategoryId is handled by the outer query.

SELECT TOP 3
    (SELECT TOP 1
        ProductId
    FROM
        Product P
    WHERE
        P.ProductCategoryId = PC.ProductCategoryId
    ORDER BY 
        NEWID()
    ) AS ProductId
FROM
    ProductCategory PC
WHERE
    EXISTS (SELECT *
        FROM
            Product Pex
        WHERE
            Pex.ProductCategoryId = PC.ProductCategoryId)
ORDER BY
    NEWID();

Answer 3

Thanks a lot guys, your help is much appreciated

Unfortunately both suggestions did not the trick.

Burbidge87's suggestion:

The returned CategoryId does not reflect the category the product belongs to. for example the query returns:

ProductId ||  ProductCategoryId
19 || 3
39 || 2
4 || 10

But the Product 19 belongs to Category 7, Product 39 belongs to Category 14, etc.

gbn's suggestions:

Your query also returns NULL values.

Well, I do see that it is a more complex task than I thought it was. Luckily it is just a 'nice to have' feature. I might spend more time on this later and will post the solution if I find one.

Anyway, again thanks a lot JJ

Answer 4

I got it now!

In Burbidge87's I added a where condition:

FROM Product p
where @CategoryID = p.ProductCategory_ProductCategoryId

that does it. Thanks again!

JJ