CODEWITHSUNDEEP
sqlsql-server
ef_
ef_

Reputation: 28

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

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: https://i.sstatic.net/d36EU.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)

Upvotes: 0

Views: 230

Answers (3)

fpsychias
fpsychias

Reputation: 83

To whom it may concern, I improved upon @Mini 's Query a bit so it stops even if the topmost parent is linked to itself and works even if a child has a smaller id from a parent:

declare @Subject as varchar(max)
set @Subject = 'Rome';

WITH SubjectCTE AS
(
    SELECT * FROM Subject
    WHERE SubjectName = @Subject
    UNION ALL
    SELECT C.*
    FROM SubjectCTE AS P
        JOIN Subject AS C
            ON P.ParentSubjectId = C.SubjectId 
    WHERE P.ParentSubjectId != P.SubjectId 
) SELECT SubjectName FROM SubjectCTE 
WHERE ParentSubjectId = SubjectId 
    OR ParentSubjectId IS NULL

Upvotes: 0

Deepshikha
Deepshikha

Reputation: 10274

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..

Upvotes: 1

Ruslan Veselov
Ruslan Veselov

Reputation: 337

This is simple to handle, just add new column HID (varchar) and fill it.

01     Europe
02     America
0101   France
0102   Italy
010101 Paris

Selecting parent:

DECLARE @childHID varchar(10) = '010101' --Paris

SELECT A.ID, A.Name
FROM Address A
WHERE A.HID = SUBSTRING(@childHID, 1, 2) -- first level

Also you can get all your branch:

SELECT *
FROM Address
WHERE HID LIKE '01%'
ORDER BY HID

Upvotes: 0

Related Questions