SQLTeam.com | Weblogs | Forums

How to determine if a category is child category


#1

Hello, my first time here. I am trying to figure out to determine if a given category is a child, so i can allow an insert operation. If it is a parent category, then I would like to exclude the choice from being present in my dropdown.
Here is my category table structure.

CREATE TABLE [dbo].[Categories](
[idCategory] [int] IDENTITY(1,1) NOT NULL,
[idParentCategory] [int] NULL,
[CategoryName] nchar NULL,
[shortDescription] nchar NULL,
[longDescription] nvarchar NULL,
[categoryFeatured] nchar NULL,
CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED

Any ideas for determining if a given category is not a parent category? Thanks for looking


#2

If there can be more than one level of parent-child relationships (i.e., if there can be grandchildren), I am guessing that you are looking for children who don't have children of there own. In other words, there can be 3 possibilities:

  1. Children who do not have any children of its own.
  2. Children who themselves have children
  3. Parent who does not have a parent.

If you are looking for category 1, then use a WHERE clause like this:

select idCategory
-- , other columns you may want from the table
from
	[dbo].[Categories] as c1
where 
	not exists
	(
		select *
		from [dbo].[Categories] c2
		where c2.idParentCategory = c1.idCategory
	)

#3

Thank you for the assist. I will test this out hopefully tonight when I get some time.


#4

ok. I have been using this script using a cte to build the category tree ( for easier understanding of the structure).
WITH CTE AS
(
SELECT idCategory, RTRIM(CAST(CategoryName as varchar(200))) AS CategoryName, idParentCategory
FROM Categories
WHERE idParentCategory = 0

UNION ALL

SELECT c.idCategory, RTRIM(CAST(p.CategoryName + ' > ' + c.CategoryName AS varchar(200))), c.idParentCategory
FROM Categories AS c
JOIN CTE AS p ON p.idCategory = c.idParentCategory

)

SELECT * FROM CTE
ORDER BY CategoryName

When I run this it lists all categories with their sub-categoeries, as it should. When I had the WHERE clause to the query like so.

WITH CTE AS
(
SELECT idCategory, RTRIM(CAST(CategoryName as varchar(200))) AS CategoryName, idParentCategory
FROM Categories
WHERE idParentCategory = 0

UNION ALL

SELECT c.idCategory, RTRIM(CAST(p.CategoryName + ' > ' + c.CategoryName AS varchar(200))), c.idParentCategory
FROM Categories AS c
JOIN CTE AS p ON p.idCategory = c.idParentCategory

)

SELECT * FROM CTE
WHERE
NOT EXISTS (SELECT * FROM CTE AS a WHERE CTE.idparentCategory=a.idCategory)
ORDER BY CategoryName

It will list just the Root Category, not the result I am looking for.
Here is the Results of the 2 queries.

Am I missing something to get the Categories without SubCategories( option1 in JamesK post)?


#5

I solved the problem. I had the filter in the WHERE clause switched around. Works now. This is solved. Thanks to JamesK for his help