var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: Tough question
I have an employee table, which i have to join with itself because I have to findout someones primary boss. An employee can have several bosses...
This is an example of the table.
I know this isnt a normalized table, but anyway... for each boss that an employee has there is one record depicting the employes boss.
In this case Mary has 2 bosses, John and Peter.
What I need is a query that returns the employee information and the primary boss( in this case the boss with the lowest id)
So for mary the query would return
Emp_iD, Emp_name, Boss_Id
3 Mary 1
Anyone know how could i do this?
set nocount on
if object_id('emp') is not null drop table emp
create table emp ( Emp_Id int, Emp_name varchar(20),Boss int)
insert emp values (1, 'John', 0)
insert emp values (2, 'Peter', 1)
insert emp values (3, 'Mary', 1)
insert emp values (3, 'Mary', 2)
select * from emp
For just Mary
from emp e1
where emp_id = 3
and Boss = (select min(boss) from emp e2 where e2.emp_id = e1.emp_id)
from emp e1
where Boss = (select min(boss) from emp e2 where e2.emp_id = e1.emp_id)
SELECT Emp_ID, MIN(Emp_name) AS Emp_name, MIN(Boss_Id) AS Boss_Id
GROUP BY Emp_ID