Results 1 to 3 of 3

Thread: Tough question

  1. #1
    Join Date
    May 2004
    Posts
    13

    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.

    Emp_Id 1
    Emp_name John
    Boss 'n/a'

    Emp_Id 2
    Emp_name Peter
    Boss 1

    Emp_Id 3
    Emp_name Mary
    Boss_Id 1

    Emp_Id 3
    Emp_name Mary
    Boss_Id 2

    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?

  2. #2
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    set nocount on
    go
    if object_id('emp') is not null drop table emp
    create table emp ( Emp_Id int, Emp_name varchar(20),Boss int)
    go

    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
    go

    For just Mary
    select e1.*
    from emp e1
    where emp_id = 3
    and Boss = (select min(boss) from emp e2 where e2.emp_id = e1.emp_id)

    OR
    for everyone

    select e1.*
    from emp e1
    where Boss = (select min(boss) from emp e2 where e2.emp_id = e1.emp_id)
    MCDBA

  3. #3
    Join Date
    Oct 2003
    Posts
    268
    SELECT Emp_ID, MIN(Emp_name) AS Emp_name, MIN(Boss_Id) AS Boss_Id
    FROM Emp
    GROUP BY Emp_ID

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •