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
1 2 3 4 5 6 7 |
DECLARE @tbl TABLE ( EntityId int, Name nvarchar(255), ParentId int ) |
And insert records to it as shown above
1 2 3 4 5 |
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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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 |