Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.
Solved
Recursive SQL Stored Procedure
Posted on 2010-09-07
I have a hierarchal data structure and the attached code will show a list of all the items linked to their parent. However I want to be able to 'walk' up the table as I will know the id I'm att BUT need to know the id's of it's parent etc upto the root.
Can someone help or explain how to do this. Thanks!
Can someone help or explain how to do this. Thanks!
With Breadcrumb( id, parentid, description) as
(
SELECT id, parentid, Description
FROM RadMenu
--where parentid IS NOT NULL
)
select parentid, id, description
from breadcrumb
order by parentid
Here's some of the data, filtered to show 1 example:
id parentid Description
57 NULL Adaptors
74 57 CompactFlash to IDE
75 57 CompactFlash to SATA
76 57 PCMCIA to CompactFlash
978 57 CFast to SATA
275 74 IDE to 1 CompactFlash Slot
525 74 IDE to 2 CompactFlash Slots
555 74 IDE to 1 CompactFlash Slot
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
4-
4
9 Comments
you are actually quite close...just make the CTE "recursive" with a UNION:http://msdn.microsoft.com/en-us/library/ms186243.aspx
did you check out the link I posted?
once you learned how to do it, you will remember it very easily ...
the trick is to "UNION" within the WITH ....
the first select in that union gives the "starting point", you have to give the condition which rows to check first.
the second select in that union joins to the CTE itself, so that has to return records based on the "parent records" returned by the first query ... and that does the recursive stuff.
if really you don't get it, post the query you get, and I will then fix it up for you
once you learned how to do it, you will remember it very easily ...
the trick is to "UNION" within the WITH ....
the first select in that union gives the "starting point", you have to give the condition which rows to check first.
the second select in that union joins to the CTE itself, so that has to return records based on the "parent records" returned by the first query ... and that does the recursive stuff.
if really you don't get it, post the query you get, and I will then fix it up for you
Active 1 day ago
This gives me just the value for the id I supplied:
WITH Breadcrumb AS (
SELECT id, parentid, Description
FROM RadMenu
UNION ALL
SELECT id, parentid, Description
FROM RadMenu AS RadMenu_1)
-- WHERE (parentid IS NOT NULL))
SELECT id, parentid, Description
FROM Breadcrumb AS Breadcrumb_1
WHERE (id = @id)
ORDER BY parentid
id, parentid, Description
806 650 APRO Hermes Series R:105MB/s & W:100MB/s
806 650 APRO Hermes Series R:105MB/s & W:100MB/s
While this
WITH Breadcrumb AS (
SELECT id, parentid, Description
FROM RadMenu
WHERE (id = @id)
UNION ALL
SELECT id, parentid, Description
FROM RadMenu AS RadMenu_1)
SELECT id, parentid, Description
FROM Breadcrumb AS Breadcrumb_1
ORDER BY parentid
Gives over 400 records, and it looks like it's returning all the rows in the database.
I can't see how to join it so it returns just the hierarchy of the id selected.
Mark
WITH Breadcrumb AS (
SELECT id, parentid, Description
FROM RadMenu
UNION ALL
SELECT id, parentid, Description
FROM RadMenu AS RadMenu_1)
-- WHERE (parentid IS NOT NULL))
SELECT id, parentid, Description
FROM Breadcrumb AS Breadcrumb_1
WHERE (id = @id)
ORDER BY parentid
id, parentid, Description
806 650 APRO Hermes Series R:105MB/s & W:100MB/s
806 650 APRO Hermes Series R:105MB/s & W:100MB/s
While this
WITH Breadcrumb AS (
SELECT id, parentid, Description
FROM RadMenu
WHERE (id = @id)
UNION ALL
SELECT id, parentid, Description
FROM RadMenu AS RadMenu_1)
SELECT id, parentid, Description
FROM Breadcrumb AS Breadcrumb_1
ORDER BY parentid
Gives over 400 records, and it looks like it's returning all the rows in the database.
I can't see how to join it so it returns just the hierarchy of the id selected.
Mark
here we go:
WITH Breadcrumb AS (
SELECT id, parentid, Description
FROM RadMenu
WHERE id = @id
UNION ALL
SELECT r.id, r.parentid, r.Description
FROM RadMenu r
-- here starts the "trick"
JOIN Breadcrumb b
ON b.id = r.parentid
)
SELECT id, parentid, Description
FROM Breadcrumb
Active 1 day ago
Thanks for that unfortunatly it just returns one row, I pass in an id of 806 and get
id, parentid, description
806 650 APRO Hermes Series R:105MB/s & W:100MB/s
rather than
id, parentid, description
806 650 APRO Hermes Series R:105MB/s & W:100MB/s
650 648 Standard Temp (0°C ~ 70°C)
648 646 Industrial Grade (SLC)
646 NULL CFast (1.0" SSD)
Where id 646 is the root record
id, parentid, description
806 650 APRO Hermes Series R:105MB/s & W:100MB/s
rather than
id, parentid, description
806 650 APRO Hermes Series R:105MB/s & W:100MB/s
650 648 Standard Temp (0°C ~ 70°C)
648 646 Industrial Grade (SLC)
646 NULL CFast (1.0" SSD)
Where id 646 is the root record
I think AngelIII meant this
;WITH Breadcrumb AS (
SELECT id, parentid, Description
FROM RadMenu
WHERE id = @id
UNION ALL
SELECT r.id, r.parentid, r.Description
FROM RadMenu r
-- here starts the "trick"
JOIN Breadcrumb b
ON r.id = b.parentid -- In each iteration, add b's parent, then b's b's parent etc
)
SELECT id, parentid, Description
FROM Breadcrumb
;WITH Breadcrumb AS (
SELECT id, parentid, Description
FROM RadMenu
WHERE id = @id
UNION ALL
SELECT r.id, r.parentid, r.Description
FROM RadMenu r
-- here starts the "trick"
JOIN Breadcrumb b
ON r.id = b.parentid -- In each iteration, add b's parent, then b's b's parent etc
)
SELECT id, parentid, Description
FROM Breadcrumb
Featured Post
Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
- Databases
- MongoDB
- NoSQL Databases
- Percona
- data recovery
- *Backups
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
- Server Hardware
- IT Administration
- Disaster Recovery
- Storage Software
- Databases
- Storage Hardware, MySQL Server, Hardware, Percona, *Backups
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses
Course of the Month11 days, 17 hours left to enroll
636 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.