The solution to your problem is the "Nested Tree Model" which is an innovative way to describe a tree or graph (tree with multiple root nodes) in an relational DB. There has been much written on this online and off (see "SQL For Smarties" by Joe Celko) which describes the implementation of the NTM as well as how to traverse the tree for reporting relationships (e.g. Who reports directly to the VP? Who is the boss of X employee's boss?) as well as how to insert, move and delete nodes from the tree.
If you receive any suggestions such as using a table which contains two employee_ids as foreign keys as the "supervisor" and "supervised" it is technically correct, but the implementation is quite slow and cumbersome because it requires deeply nested loops in order to walk up or down the tree. You'll find the NTM is the most effective way to model these relationships in an RDBMS. -dhs On Tuesday, October 22, 2002, at 04:25 PM, CHAM BELL wrote: > I am working on a DTS package to create an organizational chart in SQL > Server 2000. > > > > The table where I get my data from is called WebPhone. WebPhone > contains 5,753 employees (records). Each record has a unique > identifier called a UserID. Each record also has a SupervisorID so I > know who they report to. What I am trying to accomplish is a package > that will build a table for the entire organization so I don�t have to > make my pages in ColdFusion do it. I figure build the table nightly > and be done with it. > > > > There are several different levels (titles) of employees. > VicePresident, GeneralManager, Director, AreaManager, Manager. > > > > I needed to determine the different levels so I broke the employees > out into several tables depending on their title based on the titles > listed above. With these tables built, I ran a SQL query (as seen > below) that obtained who reports to whom in the organizational > structure. > > > > > > SELECT webphone_mgr.mgr_sbcuid, > > webphone_mgr.mgr_sup_id, > > webphone_am.am_sbcuid, > > webphone_am.am_sup_id, > > webphone_dir.dir_sbcuid, > > webphone_dir.dir_sup_id, > > webphone_gm.gm_sbcuid, > > webphone_gm.gm_sup_id, > > webphone_vp.vp_sbcuid, > > webphone_vp.vp_sup_id, > > > > FROM ((((webphone_vp INNER JOIN > > webphone_gm ON > > webphone_vp.vp_sbcuid = webphone_gm.gm_sup_id) > INNER JOIN > > webphone_dir ON > > webphone_gm.gm_sbcuid = > webphone_dir.dir_sup_id) INNER JOIN > > webphone_am ON > > webphone_dir.dir_sbcuid = > webphone_am.am_sup_id) INNER JOIN > > webphone_mgr ON > > webphone_am.am_sbcuid = > webphone_mgr.mgr_sup_id) > > > > > > The problem that I am having is that it can skip levels. Everyone > reports to the same VP, however you can have a Director who doesn�t > report to a GM, rather they report to the VP directly. I tried to > overcome the skipping by running similar queries for the VP at all > levels. This worked like a charm, but I have the same problem at > every level. Then I begin seeing duplicates. > > > > This is really a logic question more than a coding issue. Any > suggestions would be appreacited! > > > > Thanks! > > > > Cham Bell > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=6 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/sql This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting.
