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.
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:
Children who do not have any children of its own.
Children who themselves have children
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
)
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.