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 that?
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 that?
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';
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 NULL
s if employee has no manager).