I ran into the same problems so I built another table with columns called upper and lower. That way it had each persons upper and lower manager.
Josh -----Original Message----- From: CHAM BELL [mailto:chambell@;prodigy.net] Sent: Tuesday, October 22, 2002 4:25 PM To: SQL Subject: Organizational Chart 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 Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm
