Gethering full hierarchy from SQL Server table

Some time ago I’ve had to make some part of system, that uses hierarchical data stored in the database. Let’s imagine that we have an online store, which have products. That products points to one of categories. Our categories are organized as tree – creates hierarchy. The problem is, how to get all the products from category and all the subcategories in our hierarchy.

First thing we can do is just get subcategories, loop over them and get their subcategories, and so on until we reach the end of the hierarchy. It’s very simple to do, but it makes a lot of queries. In some systems database connection is disconnected and connected everytime we run a query – so we have a very bad solution.

Is it possible to get the full list of categories in one query, without looping, recursion and all this bad things? Fortunately it is.

In this short example we use a simple TestHierarchy table, that have three fields: HierarchyId (it is a primary key), Name and ParentId (it is a foreign key and of course points to our HierarchId).

Query below just answer the question.

WITH Hierarchy_cte AS (
 SELECT H1.* FROM TestHierarchy H1 WHERE HierarchyId = 1
 UNION ALL
 SELECT H2.* FROM TestHierarchy H2 JOIN Hierarchy_cte Hcte ON H2.ParentId = Hcte.HierarchyId
) SELECT * FROM Hierarchy_cte;

In this example I get a list of hierarchy table rows. If you need products for example, there is nothing complicated now. You can just join to this query with your product table and you're at home.