• --======== TEST DATA ==========

    IF OBJECT_ID('dbo.ParentRegionList') IS NOT NULL

    DROP TABLE dbo.ParentRegionList;

    CREATE TABLE [ParentRegionList](

    [RegionID] [int] NOT NULL,

    [RegionType] [nvarchar](50) NULL,

    [RegionName] [nvarchar](255) NULL,

    [ParentRegionID] [int] NULL

    )

    INSERT INTO [ParentRegionList] ([RegionID],[RegionType],[RegionName],[ParentRegionID]) VALUES (1,'Continent','Europe',0)

    INSERT INTO [ParentRegionList] ([RegionID],[RegionType],[RegionName],[ParentRegionID]) VALUES (2,'Continent','North America',0)

    INSERT INTO [ParentRegionList] ([RegionID],[RegionType],[RegionName],[ParentRegionID]) VALUES (500,'Country','The Netherlands',1)

    INSERT INTO [ParentRegionList] ([RegionID],[RegionType],[RegionName],[ParentRegionID]) VALUES (501,'Country','Germany',1)

    INSERT INTO [ParentRegionList] ([RegionID],[RegionType],[RegionName],[ParentRegionID]) VALUES (502,'Country','Belgium',1)

    INSERT INTO [ParentRegionList] ([RegionID],[RegionType],[RegionName],[ParentRegionID]) VALUES (550,'Country','United States of America',2)

    INSERT INTO [ParentRegionList] ([RegionID],[RegionType],[RegionName],[ParentRegionID]) VALUES (800,'State','New York',550)

    INSERT INTO [ParentRegionList] ([RegionID],[RegionType],[RegionName],[ParentRegionID]) VALUES (1000,'City','New York',800)

    INSERT INTO [ParentRegionList] ([RegionID],[RegionType],[RegionName],[ParentRegionID]) VALUES (1001,'City','Amsterdam',500)

    INSERT INTO [ParentRegionList] ([RegionID],[RegionType],[RegionName],[ParentRegionID]) VALUES (1002,'City','Rotterdam',500)

    INSERT INTO [ParentRegionList] ([RegionID],[RegionType],[RegionName],[ParentRegionID]) VALUES (1003,'City','Brussels',502)

    INSERT INTO [ParentRegionList] ([RegionID],[RegionType],[RegionName],[ParentRegionID]) VALUES (5000,'Neighborhood','Amsterdam Center',1001)

    INSERT INTO [ParentRegionList] ([RegionID],[RegionType],[RegionName],[ParentRegionID]) VALUES (5001,'Neighborhood','The Pijp',1001)

    INSERT INTO [ParentRegionList] ([RegionID],[RegionType],[RegionName],[ParentRegionID]) VALUES (10001,'Street','Damrak',5000)

    INSERT INTO [ParentRegionList] ([RegionID],[RegionType],[RegionName],[ParentRegionID]) VALUES (10002,'Street','Rue de Brussels',1003)

    --SELECT * FROM [ParentRegionList];

    --======== SUGGESTED SOLUTION ==========

    -- You can use a recursive CTE for this:

    DECLARE @RegionID Int = 1001;

    ;WITH SelectedLocations(RegionID, RegionType, RegionName, HierarchyLevel) AS

    (

    SELECT RegionID, RegionType, RegionName, 0 AS HierarchyLevel

    FROM dbo.ParentRegionList

    WHERE RegionID = @RegionID

    UNION ALL

    SELECT e.RegionID, e.RegionType, e.RegionName, HierarchyLevel + 1

    FROM dbo.ParentRegionList AS e

    INNER JOIN SelectedLocations d

    ON e.ParentRegionID = d.RegionID

    )

    SELECT RegionID, RegionName, HierarchyLevel

    FROM SelectedLocations

    ORDER BY RegionID, HierarchyLevel;

    /*

    In this example the search variable 1 (Europe) has to show all records that are (in)direct (top down) connected to RegionID 1 so:

    500 The Netherlands

    501 Germany

    502 Belgium

    1001 Amsterdam

    1002 Rotterdam

    1003 Brussels

    5000 Amsterdam Center

    5001 The Pijp

    10001 Damrak

    10002 Rue de Brussels

    In this example the search variable 800 (state new york) has to show all records that are (in)direct (top down) connected to RegionID 800 so:

    1000 New York

    In this example the search variable 1001 (Amsterdam) has to show all records that are (in)direct (top down) connected to RegionID 1001 so:

    5000 Amsterdam Center

    5001 The Pijp

    10001 Damrak

    Does somebody know how to solve this?

    Any suggestions are welcome!!

    */