Stored procedure select help....

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Stored procedure select help....

Author

Topic

alexjamesbrown
Starting Member

48 Posts

Posted - 2007-03-05 : 17:10:08

Hi there people...

I have a table, called tblProductCategories which maps tblProducts ProductID to a category.
in the category table, i have a column called "SubCatOf"
Basically, i want to select products that appear in the tblProductCategories table, if they either directly = @CategoryID, OR if that CategoryID is a subcat of another one. Does that make sense?

So say ProductID = 555
And in tblProductCategories we had 555, mapped to catID of 666

catID (in the tblCategories) was a subcatOf CatID 444

I have the following stored procedure so far:

------------------------

CREATE PROCEDURE spGetProductsFromCategory
(
@CategoryID INT
)

AS

BEGIN
SELECT DISTINCT tblProductCategories.ProductID

FROM
tblProductCategories,
tblProducts

WHERE
tblProducts.ProductID =
tblProductCategories.ProductID

AND
tblProductCategories.CategoryID =
@CategoryID

END

----------------------

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-05 : 17:19:51

it will be clearer if you can provide some sample data for these tables

alexjamesbrown
Starting Member

48 Posts

Posted - 2007-03-05 : 17:33:59

Ok:

tblCategories:
*Please note - a SubCatOf value of 0 indicates it is a "main" category

CategoryID | CategoryName | SubCatOf |
| | |
1 | Plates | 0 |
2 | Big Plates | 1 |
3 | Small Plates | 1 |
4 | Odd Plates | 1 |

tblProductCategories:

ProductID | CategoryID |
| |
333 | 2 |
234 | 2 |
453 | 3 |
444 | 4 |

Ok, so i have a page that has a "CatID" query string.
I call the stored proc listed above, with a CatID of 1 (the Main cat ID)
i basically want to select not only all products listed with a categoryID of 1, but all those whose categoryID is a SubCatOF 1.
does that make sense?

so all the Big Plates, Small Plates and Odd plates, fall under the CatID of 1...

Alex

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-05 : 18:09:10

This ?


declare @tblCategories table
(
	CategoryID	int, 
	CategoryName	varchar(15),
	SubCatOf	int
)
insert into @tblCategories
select	1 , 'Plates' , 0 union all
select	2 , 'Big Plates' , 1 union all
select	3 , 'Small Plates' , 1 union all
select	4 , 'Odd Plates' , 1 

declare @tblProductCategories table
(
	ProductID	int,
	CategoryID	int
)
insert into @tblProductCategories
select	333 , 2 union all
select	234 , 2 union all
select	453 , 3 union all
select	444 , 4 

declare @CatID	int

select	@CatID	= 1

select	*
from	@tblProductCategories p 
	inner join @tblCategories c
	on	p.CategoryID	= c.CategoryID
where	c.CategoryID	= @CatID
or	c.SubCatOf	= @CatID
/*

ProductID   CategoryID  CategoryID  CategoryName    SubCatOf    
----------- ----------- ----------- --------------- ----------- 
333         2           2           Big Plates      1
234         2           2           Big Plates      1
453         3           3           Small Plates    1
444         4           4           Odd Plates      1
*/

alexjamesbrown
Starting Member

48 Posts

Posted - 2007-03-05 : 18:24:01

Works perfectly
thanks very much

Alex

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-06 : 01:31:36

This might be read easier?

select	*
from	@tblProductCategories as p 
	inner join @tblCategories as c
	on	p.CategoryID	= c.CategoryID
where	@CatID IN (c.CategoryID, c.SubCatOf)

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-06 : 01:39:29

No. I prefer mine

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources