Hi,

I need some advice on recursive query. I looked at FB conference sample
on FB trees
http://www.firebirdsql.org/file/community/ppts/fbcon11/FBTrees2011.pdf
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.

WITH RECURSIVE fs_tree AS (
SELECT dept_id, dept_name FROM department
WHERE dept_id = 123
UNION ALL
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.