same table, 1 field to 2 field query

I have 2 tables: 1st holds employees (of ones in any position) and the 2nd holds manager employee relations with id numbers.

I want to write a query like

1st field: name(employee), 
2nd field: name(manager)

How can I do th开发者_JS百科at?

No nested queries required, just use standard joins:

select e.*, m.*
from
  employee e
    left join employee_managers em
      on e.id = em.emp_id
    left join employee m
      on m.id = em.man_id

Each row will contain all fields of employee (possibly several rows for one employee if it has several associated managers) and all fields of his corresponding manager (or NULLs if employee has no manager).

You can do that with one table:

 Employee
 --------
 EmployeeId int
 Name varchar(50)
 ManagerId int

ManagerId points to the manager's entry in the same table. The CEO will have a ManagerId of null. An example table definition:

create table Employees (
  EmployeeId int auto_increment primary key
, Name varchar(50)
, ManagerId int
, foreign key (ManagerId) references Employees(EmployeeId)
);

With some example data:

insert into Employees (Name) select 'The Chief';
insert into Employees (Name, ManagerId) select 'Grunt 1', 
    (select EmployeeId from Employees where Name = 'The Chief');
insert into Employees (Name, ManagerId) select 'Grunt 2', 
    (select EmployeeId from Employees where Name = 'The Chief');
insert into Employees (Name, ManagerId) select 'Secretary', 
    (select EmployeeId from Employees where Name = 'The Chief');

To find the name of the second Grunt's manager, you could query like:

select mgr.Name
from Employees mgr
inner join Employees grunt
on grunt.managerid = mgr.employeeid
where grunt.name = 'Grunt 2';