Many people may want to query the entire tree table associated content will be returned through the loop to check ... In fact, Microsoft can query with other syntax in SQL2005 or later, here is an example. --Querying parent nodes through child nodes
With
TREE as (
SELECT * FROM areas
WHERE id = 6--The child ID to query
UNION All
SELECT areas.* from areas, TREE
The WHERE TREE. PId = Areas.id
)
SELECT area from TREE-querying child nodes via parent node
With
TREE as (
SELECT * FROM areas
WHERE ID = 7--The child ID to query
UNION All
SELECT areas.* from areas, TREE
The WHERE TREE. Id = Areas.pid
)
SELECT area from TREE Query parent node through child node query result: Modify code--Query parent node through child node
DECLARE @area varchar (8000);
With
TREE as (
SELECT * FROM areas
WHERE id = 6--The child ID to query
UNION All
SELECT areas.* from areas, TREE
The WHERE TREE. PId = Areas.id
)
Select @area =isnull (@area, ') +area from the Tree order by ID
Select Area= @area
The result is: Fengtai District, Peking City, China according to the above, you can encapsulate this code as a stored procedure-----stored procedure, recursively get a tree-shaped region table string
if exists (select * from sysobjects where name= ' sp_getareastr ')
drop proc Sp_getareastr
Go
CREATE PROCEDURE Sp_getareastr
@id int
As
DECLARE @area varchar (8000)
Begin
With
TREE as (
SELECT * FROM areas
WHERE id = @id--The child ID to query
UNION All
SELECT areas.* from areas, TREE
The WHERE TREE. PId = Areas.id
)
Select @area =isnull (@area, ') +area from the Tree order by ID
Select Area= @area
End
Go
--exec sp_helptext ' Sp_getareastr '
--go
EXEC sp_getareastr 28
Go query results: Suzhou Lingbi County, Anhui Province, China table structure: Part of the data:
SQL Server Tree Table non-cyclic recursive query
