Subject Recursive query (get all employees under master department)
Author Newbie

I need some advice on recursive query. I looked at FB conference sample
on FB trees
and understand that recursive query is the way to do it, but can't fully
understand it. I have tables
department (department_id, parent_department_id, department_name) and
employee(department_id, employee_id, ..)

now I need a query to return employees of all departments and
subdepartments given department_id as input. If it is high level
department then also employees from departments under it.

SELECT dept_id, dept_name FROM department
WHERE dept_id = 123
SELECT ch.dept_id, ch.dept_name FROM department ch
JOIN fs_tree pa ON ch.parent_dept_id = pa.dept_id )

SELECT * FROM fs_tree

this gives me list of departments and sub departments. question is how
to join here employee table as well?

this query is sent from client app (Delphi), so if you think is faster
to use „normal“ CTE approach can give sample for that.