Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL stuff

Status
Not open for further replies.

ivalum21

MIS
Jul 14, 2004
63
US
Okay, this is kind of weird...

I have a table that keeps track of all of the employees for my company, we'll call it Employees. Within this table is a column for the supervisor for each employee. Since the supervisors themselves are employees, they are also in in the Employees table.

So Joe Smith is a manager, and Jon Doe is a manger underneath Joe. I need to display the employees that work under Jon....and since Joe has more than one manager working under him, I can't just use Jon's information to display his subordinates.

Basically what I'm trying to say is I need to display ALL the "Second Tier" employees under Joe Smith. So the people that work for Jon Doe, etc...

I hope I'm explaining myself clearly enough, if not, let me know and I'll try again...
 
You want to make an org chart.

We've been tossing this around too. We've come up with two methods.

1) in every employees record add a field for "supervisor" that field would contain the employees direct supervisor. in your example John doe's record would have joe smith's name in the field.

2)if you have position title (i.e. manager, line lead, worker bee, whatever) and department (shipping, receiving, bla bla bla)in the employees record you could asign priorities to the position titles.

Ceo 1
department supervisor 2
manager 3
line lead 4
worker bee 5

This is easy if you have your position titles in a seperate table already (we do).

to get a simple linear display you could easily write a sql statement joining the two tables and ordering by the number assigned to the title.

I think this is the method we'd use if and when the issue is revisited. It's less upkeep to track who works for who like in example 1.

Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so.
-Douglas Adams (1952-2001)
 
In my database I have a table called Employee. Within my Employee table I have a column called EmpNo...along with EmpName, EmpPhone, etc. ALSO in this table I have a column called SupEmpNo (Supervisor Employee Number). In that column I have the employee numbers of their supervisors.

What I need to do is write a SQL statement that takes the second tiered people that work under John Smith. So that means I need to write a query that gets all the supervisors that reports to John Smith, and then display all the people who work under them...

Does that make sense?
 
you wouldn't be using oracle by chance would ya? oracle can do that automaticaly some how.



Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so.
-Douglas Adams (1952-2001)
 
There's the classic approach

Code:
Emplist.cfm
-----------
<cfparam name="attributes.who" default="0">
<cfparam name="attributes.space" default="--">
<!--- the value of 0 above should be changed to what top level employees have for a SupEmpNo --->
<cfquery datasource="ds_name" name="getEmp">
  select * from employees
   where SupEmpNo = #attributes.who#
</cfquery>

<cfoutput query="getEmp">
  #attributes.space##EmpName#<br>
  <cf_EmpList who="#EmpNo#" space="--#attributes.space#">
</cfoutput>

But that doesn't sound like what you want...

Code:
Emplist.cfm
-----------
<cfparam name="attributes.who" default="0">
<cfset toplev="0">
<!--- the value of 0 in both of the above should be changed to what top level employees have for a SupEmpNo --->
<cfquery datasource="ds_name" name="getEmp">
  select * from employees
   where SupEmpNo = #attributes.who#
</cfquery>

<cfoutput query="getEmp">
  <cfif toplev neq supempno>
    <cfset request.toplevlist=listappend(request.toplevlist,"#EmpName#_#EmpNo#")>
  <cfelse>
    <cfset request.toplevlist="">#EmpName#</cfif>
  <cf_EmpList who="#EmpNo#">
  <cfloop list="#listsort(request.toplevlist,"textnocase")#" index="x">
    --#listfirst(x,"_")#<br>
  </cfloop><br>
</cfoutput>

The first may output

James
Lori
Mike
Suzi
Cathy
David
Heather
Mark
Steve
Nathan
John
Chuck
Clay

The second...should output

James
Cathy
Chuck
Clay
David
Heather
John
Lori
Mark
Mike
Nathan
Steve
Suzi

ALFII.com
---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.
 
Nope, I am using Access. And right now, all I'm doing is writing the SQL statement to see if I can do this, I've got a good idea on how I want to display it in Cold Fusion.
 
display all second tier employees under Joe Smith:
Code:
select tier2.EmpName
     , tier2.EmpPhone
  from (
       Employees as tier0
inner
  join Employees as tier1       
    on tier0.EmpNo
     = tier1.SupEmpNo
       )
inner
  join Employees as tier2       
    on tier1.EmpNo
     = tier2.SupEmpNo     
 where tier0.EmpName = 'Joe Smith'
order
    by tier2.EmpName
parentheses added to make access happy

rudy
SQL Consulting
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top