Path: utzoo!attcan!uunet!dino!ux1.cso.uiuc.edu!brutus.cs.uiuc.edu!apple!oracle!news From: tgreenla@oracle.uucp (Terry Greenlaw) Newsgroups: comp.databases Subject: Re: SQL Poser Message-ID: <1990Jun1.132731.6699@oracle.com> Date: 1 Jun 90 13:27:31 GMT References: <6588@umd5.umd.edu> Reply-To: tgreenla@oracle.UUCP (Terry Greenlaw) Organization: Oracle Corporation, Atlanta, GA Lines: 60 In article jkrueger@dgis.dtic.dla.mil (Jon) writes: >jay@umd5.umd.edu (Jay Elvove) writes (paraphrased): > >> consider a table >> >> +-----+ >> | emp | emp mgr >> +-----+------+------+ >> | F | D | >> | G | D | >> | D | B | >> | B | A | >> | E | C | >> | C | A | >> +------+------+ >> >> representing the hierarchy >> >> A >> / \ >> B C >> / \ >> D E >> / \ >> F G >> >> it may be assumed that each employee has exactly one manager; >> how to write a SQL query that finds the chain of command for >> an employee, e.g. given F, the chain of command is D B A > >SQL (and other query languages for RDBMS) can't express recursive >queries. So the simple answer is it can't be done. The longer answer >is (1) it can't be done in a single query, (2) nor any finite number of >queries, (3) nor any reasonable number of queries, where "reasonable" >is defined as spending more time executing queries than dispatching >them, (4) nor without writing a program in some general purpose >programming language (see below). ... 3GL deleted ... >Jonathan Krueger jkrueger@dtic.dla.mil uunet!dgis!jkrueger >Drop in next time you're in the tri-planet area! Oracle has an extension to SQL which was designed for tree traversal like the problem shown above. Two clauses were added to the select statement; connect by and start with. In the example above, you would need to add a record for employee A (The Big Cheese) with a mgr set to null. Then to traverse the tree, you would use the statement select emp from emp start with mgr is null connect by prior emp = mgr; There also is a pseudocolumn called level that reflects the current traversal depth. It's handy for formatting your output to look like a tree. Hope this helps those of you with Oracle out there. Terry O. Greenlaw Sheathed within the Walkman, Staff Engineer Wear a halo of distortion. Oracle Corporation Aural contraceptive, tgreenla@oracle.oracle.com Aborting pregnant conversation - Marillion