Elie Fadous Elie Fadous - 7 months ago 9
SQL Question

SQL - how to get the top parent of a given value in a self referencing table

I'm having this small issue.

the table basically looks like the following

Subject


  • SubjectId

  • SubjectName

  • ParentSubjectId



ParentSubjectId
references the subject table itself.
and it can go down for many levels (no specific number of levels)

example (using countries just for the sake of this example):

1.Europe
2.America
1.1 France
1.1 Italy
2.1 USA
2.2 Canada
1.1.1 Paris
1.2.1 Rome


and so on..

SubjectID is Guid
ParentSubjectID is a GUID too.

Sample Overview: http://i.imgur.com/a2u2CfT.png

it can even keep going down in levels indefinitely (maybe even to the street number level)

my question is:
given a subject (no matter the depth).
i would like to get the top parent of that subject (
Europe/America
in this case)

How can i do this ?
Is it possible using Basic SQL query ?

please note that i cannot modify the database at all (i'm querying data from an already existing database)

Answer

Write as:

declare @Subject as varchar(max)
set @Subject = 'Rome'; -- set subject name here

WITH SubjectCTE AS
(
SELECT SubjectId , SubjectName , ParentSubjectId
FROM Subject
WHERE SubjectName = @Subject 
UNION ALL
SELECT C.SubjectId , C.SubjectName , C.ParentSubjectId
FROM SubjectCTE AS P
JOIN Subject AS C
ON P.ParentSubjectId = C.SubjectId
)
,SubjectCTE2 as 
(
SELECT SubjectId , SubjectName , ParentSubjectId, 
       Row_Number() over ( order by SubjectId asc) as rownum
FROM SubjectCTE
)
select SubjectName as RequiredParentName
from SubjectCTE2
where rownum =1 

check demo here..