SUBORDINATES is the Common Table Expression(CTE) we create to evaluate the subordinates. The query has this high level format: WITH RECURSIVE SUBORDINATES AS () SELECT * FROM SUBORDINATES Here is the query: WITH RECURSIVE SUBORDINATES AS (Īnd here is the result: employee_id employee_name manager_id 5 KANNAN 2 1 VIJAY 5 6 KRITHIKA 5 3 RODRIQUES 6 10 KATHERINE 3Īll the subordinates along with the manager has been returned! Now let’s use recursive query also called Common Table Expressions to retrieve the subordinates. So the entire list of subordinates of the manager Kannan include : In turn Krithika has her own reportee Rodriques.Īnd Rodriques has his own reportee Katherine. The manager of employee Vijay is Kannan(employee id 5)Īnd the manager of employee Krithika is also Kannan Here is the list of records: employee_id employee_name manager_id 1 VIJAY 5 2 KATHIR 7 6 KRITHIKA 5 8 BANU 7 3 RODRIQUES 6 5 KANNAN 2 9 MAHESH 4 4 GANESH 9 7 RAHUL 4 10 KATHERINE 3 the manager id (who is also an employee and hence will have a separate row entry with an employee id)ĮMPLOYEE_ID INTEGER NOT NULL PRIMARY KEY,.This is supported in other relational tables as well though the syntax may vary a bit. This can be done using recursive queries. That is say Kiran reports to Guru and Gayathri reports to Kiran, then the subordinates of Guru are both Kiran and Gayathri. Let’s say you want to find out all the subordinates to a manager recursively.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |