Get matching subcategories under a parent

Transact-SQL
1

Hi,
Using sql2012. Have a category table with main and sub categories.
Need all final categories under a main category.
Tried with passing parentID,but it shows only the first level categories.

catID	categoryName	parentCatID	     isFinal
100	vessels	           NULL	              NULL
101	vesselbyShape	  100	                      NULL
102	glass vessels	   100	                Y
103	stone vessels	   100	                Y
104	vesselbyType	   100	              NULL
201	roundVessel	  101	                        Y
204	paintedVessel	    104	                    Y

DECLARE @myCategory table
(
  catID int
, categoryName varchar(25)
, parentCatID int
, isFinal char(1) 
);
INSERT  INTO @myCategory VALUES
(
	100,'vessels',NULL,NULL
),
(
	101,'vesselbyShape',100,NULL
),
(
	102,'glass vessels',100,'Y'
),
(
	103,'stone vessels',100,'Y'
),
(
	104,'vesselbyType',100,NULL
)
,
(
	201,'roundVessel',101,'Y'
)
,
(
	204,'paintedVessel',104,'Y'
)
SELECT * from @myCategory

Tried query:
SELECT
catID,
categoryName

			FROM
			  @myCategory
			WHERE
			parentCatID = 100
			AND
				 isFinal= 'y'

Expected output: 102,103,201,204
2

How do you know if it falls under main category?
You need to pass multiple ParentCatId in your where condition.

SELECT
catID,
categoryName

		FROM
		  @myCategory
		WHERE
		parentCatID is not null
		AND
			 isFinal= 'y'
3

thanks. The current table structure has immediate parent as parentCatID. Can we identify the products fall under main category using this table or should we add another mapping table?

4

I think what you have is good. you might need a tree query, recursive yaddi yadda

DECLARE @myCategory table
(
  catID int
, categoryName varchar(25)
, parentCatID int
, isFinal char(1) 
);
INSERT  INTO @myCategory VALUES
(
	100,'vessels',NULL,NULL
),
(
	101,'vesselbyShape',100,NULL
),
(
	102,'glass vessels',100,'Y'
),
(
	103,'stone vessels',100,'Y'
),
(
	104,'vesselbyType',100,NULL
)
,
(
	201,'roundVessel',101,'Y'
)
,
(
	204,'paintedVessel',104,'Y'
)

create table #parents(ParentID int null)
insert into #parents
SELECT catID
FROM @myCategory
WHERE isFinal = 'Y'
  and parentCatID is not null

;WITH tree (catID, categoryName, parentCatID, isFinal) as 
(
SELECT catID, categoryName, parentCatID, isFinal
FROM #parents p join @myCategory mc on p.ParentID = mc.catID
UNION ALL
SELECT e.catID, e.categoryName, e.parentCatID, e.isFinal
FROM @myCategory e
INNER JOIN tree ptree ON ptree.catID = e.parentCatID 

)
SELECT *
FROM tree

drop table #parents