Stack Table Problem With Join & random Component - Tough One for an SQL champion.
Posted on 2004-10-23
This is quite a complex problem.
Basically I have a table called categories that looks like this
category_id INT (pkey)
category_name VARCHAR (50)
cateogry_description VARCHAR (500)
As you might imagine, categoris can have subcategories etc.. and the parent_id field reflects the subcategories' parent category id. Pretty simple.
Top level categories have a parent_id of 0 (the default value of the field when a new record is created)
But wait, there's more.
I have another table called products:
product_id INT (pkey)
category_id INT (Fkey category_id)
stock_status_id (INT) (fkey stock_status_id)
in this way a product can be a child of a subcategory, even though it's in another table...
The application rules only allow a category to contain EITHER products or other subcategories but not both.
I have the need to build a stored procedure that when passed in a category ID will return N number of products, randomly, from the products table, provided that the category_id's of the products are descendants of the passed in category_id.
For example Given the category structure:
For 10 Speeds, there might be say 50 products in the products table. If I passed in the ID for Racing Bikes, I would want to get N number of products, randomly selected from
the products under both 10 speeds and 15 speeds.
Similarly, if given simply the ID for Bikes, all products 10 speeds, 15 speeds, brand A and Brand B would be eligible as a pool to randomly select N from.
It's further complicated by the need to only select those items which are in stock (stock_status_id=4).
I hope I have explained this in enough detail, It's a dire situation and I sure could use the help! I have alreadt trolled the q's here and checekd out the MSDN article that deals with stack tables... it didn't seem to make sense in this scenario.
Thanks In advance,