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)

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