3 random items from 3 categories and pretending to be from the top category

I thought I had it when I wrote:

declare @id int
set @id = 1

SELECT *
FROM TBLCategory as c, TBLProduct
WHERE Parent_id = @id
AND
TBLProduct.Product_id IN
(
SELECT TOP 3 p.Product_id
  FROM TBLCategory as a, TBLCategory as b, TBLProduct as p
  WHERE
  (
    (a.Parent_id = c.Category_id AND b.Parent_id = a.Category_id)
    OR
    (a.Category_id = b.Category_id AND a.Parent_id = c.Category_id)
  )
  AND
   p.Product_id IN
    (SELECT TOP 3 TBLProduct.Product_id
    FROM TBLProduct INNER JOIN TBLProductCategory ON TBLProduct.Product_id = TBLProductCategory.Product_id
    WHERE TBLProductCategory.Category_id = b.Category_id)
  ORDER BY substring(cast(rand(datepart(ms, getdate()) * Product_id) as varchar), 5, 4)

)

However this gave me 3 items but I expected a total of 6. It returned me 3 items which were seemed to come from a category but it also contained 1 item out of another category.

CJ

What version of SQL Server?

7 on the development server.

but might be 2000 in the production server (is still unsure)

CJ

500 additional points if you can solve it within an hour (then I'm leaving for home).

CJ

Try this :

select *
from TBLCategory c
join TBLCategory subc
on subc.parentid = c.catid
join TBLProductCategory pc
on pc.Category_id = subc.Category_id
join TBLProduct p
on p.catid = pc.catid
where c.parentid = @cat
and p.prodid in
(
select top 3 hp.prodid
from TBLProduct hp
join TBLProductCategory hpc
on hp.Product_id = hpc.Product_id
where hpc.catid = pc.catid
ORDER BY substring(cast(rand(datepart(ms, getdate()) * hp.Product_id) as varchar), 5, 4)
)

Hope i didn't mess up the joins, but the main idea is to use the subselect with TOP 3 correlated with the main query, that one WITHOUT the TOP 3 clause.

CHeers
CHeers

That returns me 3 items on each subcategory and 6 items of the category (2 subcategories * 2 = 6). I need 3 items in the category.

You might've solved it though, but there was one little error in your comment (don't know if that is what made the query not work here).

join TBLProduct p on p.product_id = pc.product_id

CJ

>> Hope i didn't mess up the joins, but the main idea is to use the subselect with TOP 3 correlated with the main query, that one WITHOUT the TOP 3 clause.


that is what I did in the first query which works as it should. I thought I could do the same trick again, but it didnt' :-(

Here's some info:

Table TBLproduct:
- product_id
- product_name

Table TBLProductCategory (is a LUT)
- Category_id
- Product_id

Table TBLCategory
- Category_id
- Parent_id (FK to Category_id)
- Name

Here's some sample data:
TBLCategory:
     1          Wonen
     2          Slapen
     3          Eten
     4          Werken
     5     1     Tafels
     6     1     Stoelen
     7     1     Banken
     8     1     Kasten
     9     2     Bedden
     10     2     Klerenkasten
     11     2     Dekbedden
     13     2     Verlichting
     14     3     Eettafel's
     15     3     Keukenstoelen
     16     3     Kasten
     17     3     Bestek
     18     4     Bureau's
     19     4     Bureaustoelen
     20     4     Opbergsystemen
     21     7     Driezitters
     22     7     Tweezitters
     23     8     Kastlampen

TBLProductCategory:
     21     2
     21     3
     21     5
     21     6
     22     6
     22     7
     22     8
     22     9
     23     10

TBLproduct:
Product_id     Name
2     Dressoir Leie
3     Dressoir Leue 2
5     Dressoir Leie 3
6     Dressoir Leie 2 zits
7     Dressoir Leie 2 - 2 zits
8     Dressoir Leie 3 - 2 zitsbank
9     Dressoir Leie 4 - 2 zits
10     Lampje
     

obviously, we have to go one step further in the subselect:

select *
from TBLCategory c
join TBLCategory subc
on subc.parentid = c.catid
join TBLProductCategory pc
on pc.Category_id = subc.Category_id
join TBLProduct p
on p.product_id= pc.product_id
where c.parentid = @cat
and p.prodid in
(
select top 3 hp.prodid
from TBLProduct hp
join TBLProductCategory hpc
on hp.Product_id = hpc.Product_id
join TBLCategory hsubc
on hpc.catid= hsubc.catid
join TBLCategory hc
on hsubc.parentid = hc.catid

where hpc.catid = pc.catid
ORDER BY substring(cast(rand(datepart(ms, getdate()) * hp.Product_id) as varchar), 5, 4)
)

Again, I didn't replace all the catid by category_id etc, as i don't have a test environment.

CHeers

Output wanted:

Insert 1

and retrieve 3 items from
2     Dressoir Leie
3     Dressoir Leue 2
5     Dressoir Leie 3
6     Dressoir Leie 2 zits
7     Dressoir Leie 2 - 2 zits
8     Dressoir Leie 3 - 2 zitsbank
9     Dressoir Leie 4 - 2 zits

and retrieve 1 item
10     Lampje

is a total of 4 returned items.

I hope it does make some sense :-/

CJ

Here the verified version:

declare @cat int
set @cat = 1


select *
from TBLCategory c
join TBLCategory subc
on subc.parent_id = c.category_id
join TBLProductCategory pc
on pc.Category_id = subc.Category_id
join TBLProduct p
on p.product_id= pc.product_id
where c.parent_id = @cat
and p.product_id in
(
select top 3 hp.product_id
from TBLProduct hp
join TBLProductCategory hpc
on hp.Product_id = hpc.Product_id
join TBLCategory hsubc
on hpc.category_id= hsubc.category_id
join TBLCategory hc
on hsubc.parent_id = hc.category_id

where hpc.category_id = pc.category_id
ORDER BY substring(cast(rand(datepart(ms, getdate()) * hp.Product_id) as varchar), 5, 4)
)

CHeers

Thus ignore the above...

Same output.

I really think there should be 2 subqueries in there.

1) a subquery to retrieve 3 items per subcategory
2) a subquery which retrieves 3 random items out of all subqueries (1).

Still same output :-(

Banken
Banken
Banken
Banken
Banken
Banken
Kasten

I get 6 items out of category "Banken", while there should be only 3.

CJ

the key is this line:
>> where hc.category_id = c.category_id
as you want 3 products per category

CHeers

phew, that was short (i'm within the hour) :)

You're my hero!!!! Thanks angellll

Look for a Q with your name.

CJ

angelll,
 you can earn an additional 500 points. ;)

What change has to be made if I also want items included from the category itself (ie, if it has no subcategories).

CJ

You mean, if there are products directly in the category?
Let me check...

Exactly. It may occur, working on it myself also. No luck yet.

Simple, add some OR in the correct joins, to have the join also possible directly between the cat and the product(category):

declare @cat int
set @cat = 1


select c.*, p.*
from TBLCategory c
join TBLCategory subc
on subc.parent_id = c.category_id
join TBLProductCategory pc
on pc.Category_id = subc.Category_id
or pc.Category_id = c.Category_id   -- here
join TBLProduct p
on p.product_id= pc.product_id
where c.parent_id = @cat
and p.product_id in
(
select top 3 hp.product_id
from TBLProduct hp
join TBLProductCategory hpc
on hp.Product_id = hpc.Product_id
join TBLCategory hsubc
on hpc.category_id= hsubc.category_id
join TBLCategory hc
on hsubc.parent_id = hc.category_id
or hpc.category_id = hc.category_id    -- here
where hc.category_id = c.category_id
ORDER BY substring(cast(rand(datepart(ms, getdate()) * hp.Product_id) as varchar), 5, 4)
)


CHeers

darn, I thought I had to do that on the main query and not on the subquery :-( Probably why.

Anyway, again solved. Another question will be opened. look for it shortly.

CJ

Thjere's a little flaw with the last SQL query. I tested it with a category and subcategory. When a product exists in a category and that category has subcategories it works. but when it has no subcategories. It does not...

CJ

left JOIN might solve the problem ?!

select c.*, p.*
from TBLCategory c
left join TBLCategory subc   -- here
on subc.parent_id = c.category_id
join TBLProductCategory pc
on pc.Category_id = subc.Category_id
or pc.Category_id = c.Category_id
join TBLProduct p
on p.product_id= pc.product_id
where c.parent_id = @cat
and p.product_id in
(
select top 3 hp.product_id
from TBLProduct hp
join TBLProductCategory hpc
on hp.Product_id = hpc.Product_id
join TBLCategory hsubc
on hpc.category_id= hsubc.category_id
left join TBLCategory hc    -- here
on hsubc.parent_id = hc.category_id
or hpc.category_id = hc.category_id
where hc.category_id = c.category_id
ORDER BY substring(cast(rand(datepart(ms, getdate()) * hp.Product_id) as varchar), 5, 4)
)

This time, i was out for lunch (with the girlfriend thus not within an hour :-)

CHeers

And again he solves! Thanks!

I hope those 6000 points were enough for this little service ;-)

CJ

yes!