help with query - employee heirarchy

[scottydel]

Hello,

I have an employee table set up like so:

NAME, SALRY, SUPERVISOR_NAME

This table contains a large group of employees, and every employee has a Name, Salary and Supervisor. Some of these employees are also Supervisors, and hence they are in the table once as an employee but also have their name multipe times under the SUPERVISOR_NAME field. But not all employees are Supervisors. So if "Joe" is a Supervisor a section of the table would look something like:

Alan $12,000 Joe
Sue $14,000 Joe
Rob $19,000 Joe

"Joe" would also have his own record in the table somewhere else and have a Supervisor of his own.

Joe $22,000 Fred
Rex $48,000 Fred
Sam $12,000 Sue

The top-level person in the table has themself listed as their Supervisor. There are a large number of employees who are not Supervisors, and I need to do some data manipulation on these employees (and be able to check whether or not any employee is a Supervisor or not). So these "subordinate-only" employees have a record for their Name, Salary and Supervisor, but nowhere else in the table are they listed as anyone else's Supervisor.

I would like to set up a query that, in English, says: Show me all of the names of the employees who are NOT supervisors. Or (maybe using parameters), is this particular employee a Supervisor?

Any ideas?

Thanks,

Scott

 

[ByteMyzer]

Try something like the following (substitute highlighted text with actual table name):

SELECT T1.NAME, T1.SALRY,
  Iif(IsNull(T2.SUPERVISOR_NAME),'No','YES') AS IsSupervisor
FROM [b][i]MyTable[/i][/b] T1 LEFT JOIN
 (SELECT DISTINCT SUPERVISOR_NAME
  FROM [b][i]MyTable[/i][/b]) T2 ON T1.NAME = T2.SUPERVISOR_NAME
;

 

[ajetrumpet]

I would like to set up a query that, in English, says: Show me all of the names of the employees who are NOT supervisors.

Scotty, how about a simple subquery to get what you need here??

SELECT employees.name, employees.salary, employees.supervisor_name
FROM employees
WHERE employees.name NOT IN
(SELECT employees.supervisor_name
FROM employees);

You could even attach this query to a report and call it something like "Subordinate List"

 

[scottydel]

Thank you both for the responses, both approaches are useful to me.

-Scott