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. ------------------------------------ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: [email protected] [email protected] <*> To unsubscribe from this group, send an email to: [email protected] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
