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

 

And insert records to it as shown above

 

Now let’s write an SQL-expression

 

The result is:

EntityId

Name

ParentId

2

Dep2

1

3

Dep3

1

4

Dep4

1

5

Dep5

2

 



MarkiMarta.com. Notes of web-specialist
Since 2009
18+