Path: utzoo!attcan!uunet!cimshop!davidm From: cimshop!davidm@uunet.UU.NET (David S. Masterson) Newsgroups: comp.databases Subject: Re: SQL Poser Message-ID: Date: 31 May 90 17:12:52 GMT References: <6588@umd5.umd.edu> Sender: davidm@cimshop.UUCP Organization: Consilium Inc., Mountain View, California. Lines: 25 In-reply-to: jay@umd5.umd.edu's message of 30 May 90 19:50:03 GMT In article <6588@umd5.umd.edu> jay@umd5.umd.edu (Jay Elvove) writes: Given a two-column table, where column one is a primary key designating an employee and column two designates his boss, and columns one and two are identical when an employee is his/her own boss; using SQL, how would one go about displaying the chain of command of each employee in the table? Assume each employee can only have one boss. Unless you assume a maximum length of the chain of command, then it can't be done. This is one of the shortcomings of relational languages (like SQL) that I've seen so far. It comes under the title of the "parts explosion" (I think that's the example most people use) which given a part constructed from subparts, then show what parts will be affected downline by a change in a subpart. If you make an assumption about the maximum length (say N), then you can outer join the table with itself N-1 times to produce the answer. BTW, this outer join would not care whether an employee had one or more than one bosses. -- =================================================================== David Masterson Consilium, Inc. uunet!cimshop!davidm Mt. View, CA 94043 =================================================================== "If someone thinks they know what I said, then I didn't say it!"