Select items in a category and its sub categories

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 2008 Forums

Transact-SQL (2008)

Select items in a category and its sub categories

Author

Topic

henryvuong
Starting Member

8 Posts

Posted - 2014-07-05 : 23:54:22

I have a Categories table and Items table like these:

Categories


CategoryID 	CategoryName	CategoryLevel	OrdinalNumber
1		All 			0		1
2		iOS			1		2
3		Android			1		5
4		Sub iOS 1		2		3
5		Sub Andoid 1		2		6
6		Others			1		8
7		Sub Andoid 2		2		7
8		Sub iOS 2		2		4

Items


ItemID		ItemName	     CategoryID
1		iPhone 5		4
2		Motorola Droid	        5
3		iPad 2		        8
4		Samsung S1	        7
5		iPhone 4		4
6		iPad Air		8
7		Samsung S3	        7
8		Dell Laptop	        6

As you can see, they can be join by CategoryID. The Categories table, if sorted by OrdinalNumber and CategoryLevel, has tree-view display:


CategoryID 	CategoryName	CategoryLevel	OrdinalNumber
1	        All 	               0	    1
2	          iOS	               1	    2
4	           Sub iOS 1	       2	    3
8	           Sub iOS 2	       2	    4
3	          Android	       1	    5
5	           Sub Andoid 1	       2	    6
7	           Sub Andoid 2	       2	    7
6	          Others	       1	    8

Now I need an SQL command that can display all items belong to a category and its subcategories. For example, if I choose CategoryID=2 I will get:
iPhone 5
iPad 2
iPhone 4
iPad Air

If I choose CategoryID=3 I will get:
Motorola Droid
Samsung S1
Samsung S3

If I choose CategoryID=6 I will get:
Dell Laptop

And if I choose CategoryID=1 I will get all items. I am thinking of using Common Table Expression but can not figure out how to do it.

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-07-06 : 04:54:34

[code]
DECLARE @iCateg AS INT
SET @iCateg = 2
--SET @iCateg = 3
--SET @iCateg = 6
--SET @iCateg = 1

;with Categories(CategoryID,CategoryName,CategoryLevel,OrdinalNumber)
AS
( SELECT 1,'All',0,1 UNION ALL
SELECT 2,'iOS',1,2 UNION ALL
SELECT 3,'Android',1,5 UNION ALL
SELECT 4,'Sub iOS 1',2,3 UNION ALL
SELECT 5,'Sub Andoid 1',2,6 UNION ALL
SELECT 6,'Others',1,8 UNION ALL
SELECT 7,'Sub Andoid 2',2,7 UNION ALL
SELECT 8,'Sub iOS 2',2,4)

,
Items (ItemID,ItemName,CategoryID)
AS
(SELECT 1,'iPhone 5',4 UNION ALL
SELECT 2,'Motorola Droid', 5 UNION ALL
SELECT 3,'iPad 2',8 UNION ALL
SELECT 4,'Samsung S1',7 UNION ALL
SELECT 5,'iPhone 4',4 UNION ALL
SELECT 6,'iPad Air',8 UNION ALL
SELECT 7,'Samsung S3',7 UNION ALL
SELECT 8,'Dell Laptop',6 )

,RecCategories
AS(
SELECT
C.CategoryID
,C.OrdinalNumber
,C.CategoryLevel
FROM Categories AS C
WHERE C.CategoryID = @iCateg

UNION ALL

SELECT
C.CategoryID
,C.OrdinalNumber
,C.CategoryLevel
FROM
RecCategories AS RC
INNER JOIN Categories AS C
ON RC.OrdinalNumber + 1 = C.OrdinalNumber
AND ((RC.CategoryLevel <= C.CategoryLevel)
OR (select CategoryLevel from Categories where CategoryID = @iCateg) = 0)
)

SELECT
I.ItemName
FROM Items AS I
WHERE
I.CategoryID IN (SELECT RC.CategoryID
FROM RecCategories AS RC)
[/code]

sabinWeb MCP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources