>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.

If what you're asking is what I think, then the answer is very simple:

(same CTE as you already have)

SELECT * FROM fs_tree fs
JOIN employee e on fs.dept_id = e.dept_id

Of course, you could also put this in the CTE itself (then you might have to do it both before and after UNION ALL), but you haven't mentioned any "employee recursion" (e.g. list all employees whose leaders or their leaders belong to the department, regardless of whether the employee or his closest leader belong to the department), so there isn't much reason to complicate things.

HTH,
Set