asked on

SQL Query - Get parent hierarchy by supplying child ID

I have a standard hierarchical table as follows:

Id (Int, PK)
ParentId (Int)
Category (Varchar)

For top level categories, the ParentId = 0 rather than NULL.

I want to supply a category Id and return a set of rows which I can use in code to assemble an ordered list in the following format:

- A1
- A2
- A3
---- A3.1
---- A3.2
-------- A3.2.1
---- A 3.3
- A4
- A5

In this case, I want to supply the ID for A3.2.1 and retrieve its parent as well as any categories at the same level as the parent and so on. I only need a simple table of categories (they don't have to be in a particular order) as the structuring of the list will be done in separate code.

How can I do this with a stored procedure?

rbeadie

Try this:

WITH ParentChild (ParentID, ParentCategory) AS
(
SELECT C.ID as ParentID, isnull(nullif(P.Category +'.','.'),'')+ C.Category as ParentCategory
FROM eeTest C LEFT JOIN eetest P ON C.ParentID = P.ID
)
SELECT A.ID, isnull(nullif(B.ParentCategory +'.','.'),'')+ A.Category
FROM MyTable A
LEFT JOIN ParentChild B on A.ParentID = B.ParentID

It should work with any number of levels

rbeadie

Correction: the query above works down only three levels. This one will go to five levels:

WITH ParentChild (ID, ParentID, ParentCategory) AS
(
SELECT C.ID, G.ID as ParentID, isnull(nullif(P.Category +'.','.'),'')+ C.Category as ParentCategory
FROM MyTable C LEFT JOIN eetest P ON C.ParentID = P.ID
LEFT JOIN MyTable G ON P.ParentID = G.ID
)
SELECT A.ID, isnull(nullif(C.ParentCategory +'.','.'),'')+ isnull(nullif(B.ParentCategory +'.','.'),'')+ A.Category
FROM MyTable A
LEFT JOIN ParentChild B on A.ParentID = B.ID
LEFT JOIN ParentChild C on B.ParentID = C.ID
GO

pipelineconsulting

ASKER

What is "eetest"?

Also how is the category parameter supplied?

rbeadie

Oops -- sorry, that is the name of the table I created. It should be:

WITH ParentChild (ID, ParentID, ParentCategory) AS
(
SELECT C.ID, G.ID as ParentID, isnull(nullif(P.Category +'.','.'),'')+ C.Category as ParentCategory
FROM MyTable C LEFT JOIN MyTable P ON C.ParentID = P.ID
LEFT JOIN MyTable G ON P.ParentID = G.ID
)
SELECT A.ID, isnull(nullif(C.ParentCategory +'.','.'),'')+ isnull(nullif(B.ParentCategory +'.','.'),'')+ A.Category
FROM MyTable A
LEFT JOIN ParentChild B on A.ParentID = B.ID
LEFT JOIN ParentChild C on B.ParentID = C.ID
GO

And there is no parameter supplied -- the WITH clause is new syntax for SQL Server.

pipelineconsulting

ASKER

Not sure that's quite what I need. I'm new to CTEs but I've put together the CTE below. This takes an Id as a parameter (@Id) and retrieves the direct ancestors.

However, what I need to be able to do is supply the Id, retrieve the parent and the parent's siblings, then the parent's parents and parent's parents' siblings and so on.

(
	@Id int
)
	
AS 
	SET NOCOUNT ON; 

WITH CTE_Hierarchy AS 
(
	SELECT	c.Id, c.ParentId, c.Category FROM Categories c WHERE c.Id = @Id
	UNION ALL
	
	SELECT d.Id, d.ParentId, d.Category FROM CTE_Hierarchy
	JOIN Categories d ON d.Id = CTE_Hierarchy.ParentId
)

SELECT * FROM CTE_Hierarchy;

Open in new window

ASKER CERTIFIED SOLUTION

Thomasian

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial