Solved

Recursive SQL Stored Procedure

Posted on 2010-09-07
9
683 Views
Last Modified: 2012-08-13
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!
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

Open in new window

0
Comment
Question by:markej
  • 4
  • 4
9 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33618195
you are actually quite close...just make the CTE "recursive" with a UNION:http://msdn.microsoft.com/en-us/library/ms186243.aspx
0
 

Author Comment

by:markej
ID: 33618387
How do I do that? and how to I pass in the id of the item I want to work up from?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33618433
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
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

Author Comment

by:markej
ID: 33619567
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
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33619982
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 

Open in new window

0
 

Author Comment

by:markej
ID: 33620618
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

0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 250 total points
ID: 33625511
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
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 250 total points
ID: 33625533
oh yes... the parents ... not to show the children...
I didn't see that (too busy on job)

thanks, cyberkiwi
0
 

Author Closing Comment

by:markej
ID: 33625586
Still not quite sure how this works BUT will try and use this as an example for any further requirements
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
ServiceCenter IR Query Expressions 1 23
MySQL Backup Strategy 15 26
Sort GridView by ID Descending 1 13
T-SQL:  Collapsing 9 25
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question