Examples of Self-connection to SQL Server databases

 

Create a database environment

 use master
go
if not exists(select * from sysdatabases where name='stuDB')
create database stuDB
go

 

Create a data table

 use stuDB
go
create table AreaTable
(
   Subid int primary key,
   AddName varchar(16),
   ParentId int null
)
go

 

Add data to the new table

Add data

 Insert into AreaTable
Select 86, 'China', null union
Select 430731, 'changsha City ', 43 union
Select 43, 'hunan province ', 86 union
Select 43073101, 'changsha County ', 430731 union
Select 43073102, 'ningxiang County ', 430731 union
Select 430732, 'yueyang City ', 43 union
Select 43073202, 'pingjiang', 430732 union
Select 43073103, 'xiangyin County ', 430732
Go

Select * from AreaTable

 

 

Solution:Tree hierarchy display
/*
This is a region table that stores the region name and its parent region. Assume that you need to query the region and its parent region.
*/

Method 1 of Self-connection:

 Select AddName as 'region name ',
(Select AddName from AreaTable as
Where a. Subid = B. ParentId) as 'Upper-level region name'
From AreaTable as B

 

Method 2 of Self-connection:

 Select a. AddName as 'region name ',
B. AddName as 'Upper-level region name'
From AreaTable as
Left join AreaTable as B
On a. ParentId = B. Subid

 

Self-connection level 3 (left join ):

Self-connection level 3 (left join)

 Select a. AddName as 'region name ',
B. AddName as 'superior name ',
C. AddName as 'Upper-level name'
From AreaTable as
Left join AreaTable as B
On a. ParentId = B. Subid
Left join AreaTable as c
On B. ParentId = c. Subid

 

Self-connection level 4 (internal link ):

Self-connection level 4 (internal link)

 Select a. AddName as 'hangzhou ',
B. AddName as 'region ',
C. AddName as 'province ',
D. AddName as 'status'
From AreaTable as
Inner join areatable as B
On a. parentid = B. subid
Inner join areatable as C
On B. parentid = C. subid
Inner join areatable as d
On C. parentid = D. subid