Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
With CTE we can generate recursive queries.
We can create a code block and use it over and over again.
In this article, I will illustrate by example how to obtain hierarchical results using CTE.
We create a table using the following script.
1 2 3 4 5 6 7 8 | CREATE TABLE employee( employeeid smallint NOT NULL, name varchar(30) NOT NULL, surname varchar(40) NOT NULL, title varchar(50) NOT NULL, departmentid smallint NOT NULL, managerid smallint NULL ); |
The following script also adds a few records to the table.
1 2 3 4 5 | INSERT INTO employee (employeeid, name, surname, title, departmentid, managerid) VALUES (1, N'Nurullah', N'ÇAKIR', N'Team Leader', 1, NULL) ,(2, N'John', N'STATHAM', N'Database Manager', 1, 1) ,(3, N'Keanu', N'REAVES', N'Senior Database Adnimistrator', 1, 2) ,(4, N'Britney', N'SPEARS', N'Junior Database Adnimistrator', 1, 3); |
With the following script, we create a CTE that creates a hierarchy of all employees.
1 2 3 4 5 6 7 8 9 | WITH RECURSIVE CTE_Example as ( select name,employeeid,managerid,title from employee where employeeid=4 union all select e.name,e.employeeid,e.managerid,e.title from employee e join CTE_Example on e.employeeid=CTE_Example.managerid ) select * from CTE_Example; |
