Recursive SQL Query

Question

First of all thanks for the help , I'm no expert on sql and maybe do a very basic question

my database is SQL Server 2012

I have a table with the fields shown below

servicerequestareaid - enumtypeid              -value             -ordinal -    parentid
69  - D115C073-613F-F8C4-04A1-DE63B28D2496 -    Servidor Exchange - 0          - 68
70  - 3A09DA42-B33E-05EA-C2CB-167FECBDE346 -    Rol CAS           - 0          - 69
71  - E9AFC8C0-76F3-2B92-38A3-7A5B0F9FCD07 -    Perimetral        - 0          - 70
72  - 925F6D8C-EA24-798F-3D6C-EB64AC436D6F -    Asegurado         - 0          - 71

I need to take the value of servicerequestareaId , enumtypeId and parentId field for that record

i begin with a value of servicerequestareaid,

parentid value corresponds to the value stored in the servicerequestareaid field to another record

this process is repeated until the value stored in parentId is equal to 1

after having that set of values ​​stored must display

create two cursors for this purpose but do not want to do it that way , should habrer a more efficient way to do

![Cursor][2]

---

---

thanks for the help

Answer 1

Do it in recursive cte:

DECLARE @t TABLE(id INT, parent_id int)

INSERT INTO @t VALUES
(1, null),
(2, null),
(3, 1),
(4, 3),
(5, 2),
(6, 2),
(7, 5)


DECLARE @id INT = 7

;WITH cte AS(
SELECT * FROM @t WHERE id = @id
UNION ALL
SELECT t.* FROM @t t JOIN cte ON t.id = cte.parent_id
)
SELECT * FROM cte

Output:

id  parent_id
7   5
5   2
2   NULL

I think you will easily adjust this to your tables.