Nice way to select all the child elements in SQL

The task is to select all the records where parent equal ‘1’ and parent of parent equal ‘1’ etc. Our datatable is:

EntityId	Name	ParentId
1	Dep1	0
2	Dep2	1
3	Dep3	1
4	Dep4	1
5	Dep5	2

Let’s create a temporary table

DECLARE @tbl TABLE
(

	EntityId int,
	Name nvarchar(255),
	ParentId int
)

DECLARE @tbl TABLE

(

EntityId int,

Name nvarchar(255),

ParentId int

)

And insert records to it as shown above

INSERT into @tbl (EntityId, Name, ParentId) SELECT 1, 'Dep1', 0
INSERT into @tbl (EntityId, Name, ParentId) SELECT 2, 'Dep2', 1
INSERT into @tbl (EntityId, Name, ParentId) SELECT 3, 'Dep3', 1
INSERT into @tbl (EntityId, Name, ParentId) SELECT 4, 'Dep4', 1
INSERT into @tbl (EntityId, Name, ParentId) SELECT 5, 'Dep5', 2

INSERT into @tbl (EntityId, Name, ParentId) SELECT 1, 'Dep1', 0

INSERT into @tbl (EntityId, Name, ParentId) SELECT 2, 'Dep2', 1

INSERT into @tbl (EntityId, Name, ParentId) SELECT 3, 'Dep3', 1

INSERT into @tbl (EntityId, Name, ParentId) SELECT 4, 'Dep4', 1

INSERT into @tbl (EntityId, Name, ParentId) SELECT 5, 'Dep5', 2

Now let’s write an SQL-expression

DECLARE @ID Int
SELECT @ID = 1

;WITH results AS(
    	SELECT	*
    	FROM	@tbl
    	WHERE	ParentId = @ID
    	UNION ALL
    	SELECT	t.*
    	FROM	@tbl t INNER JOIN
    			ret r ON t.ParentId = r.EntityId
)

SELECT  *
FROM    results

DECLARE @ID Int

SELECT @ID = 1

;WITH results AS(

SELECT *

FROM @tbl

WHERE ParentId = @ID

UNION ALL

SELECT t.*

FROM @tbl t INNER JOIN

ret r ON t.ParentId = r.EntityId

)

SELECT *

FROM results

The result is:

EntityId	Name	ParentId
2	Dep2	1
3	Dep3	1
4	Dep4	1
5	Dep5	2

16 July 2013, admin
SQL
Comments: (0)

Nice way to select all the child elements in SQL

No Comments »

Leave a comment

New posts