• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1991
  • Last Modified:

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?
0
pipelineconsulting
Asked:
pipelineconsulting
  • 3
  • 2
1 Solution
 
rbeadieCommented:
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
0
 
rbeadieCommented:
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
0
 
pipelineconsultingAuthor Commented:
What is "eetest"?

Also how is the category parameter supplied?
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
rbeadieCommented:
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.
0
 
pipelineconsultingAuthor Commented:
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

0
 
ThomasianCommented:
Here you go
WITH CTE_Hierarchy AS 
(
        SELECT  c.Id, c.ParentId, c.Category, 0 Ancestor FROM Categories c WHERE c.Id = @Id
        
        UNION ALL
        
        (
        SELECT d.Id, d.ParentId, d.Category, 1 FROM CTE_Hierarchy
        JOIN Categories d ON d.Id = CTE_Hierarchy.ParentId
        WHERE Ancestor IS NOT NULL
		
        UNION ALL

        SELECT d.Id, d.ParentId, d.Category, NULL FROM CTE_Hierarchy
        JOIN Categories d ON d.ParentId = CTE_Hierarchy.ParentId AND d.Id <> CTE_Hierarchy.Id 
        WHERE Ancestor = 1 AND d.ParentId <> 0
        )
)
SELECT Id, ParentId, Category FROM CTE_Hierarchy;

Open in new window

0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now