Results 1 to 11 of 11

Thread: self join help

  1. #1
    Join Date
    Oct 2003
    Posts
    7

    Unanswered: self join help

    Hi everyone,
    I need help in self join
    here is the data
    select id,lastname,supervisor from employee;

    ID LASTNAME SUPERVISOR
    ----- ------------------------------ ----------
    1 Menon 1
    2 Seema 1
    3 Brad 1
    4 Chaya 3
    5 ABC 4
    6 Scott 2

    I want to find out how many employees work for each supervisor
    and the name of supervisor

    I got this query working
    select count(emp.id),emp.supervisor
    2 from employee emp,employee sup
    3 where emp.id = sup.id
    4 group by emp.supervisor
    5 order by count(emp.id);
    but when i add name field it gives me incorrect data..
    Thanks for the help

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    SELECT s.lastname supervisor_name, COUNT(e.lastname) number_of_employees
    FROM EMPLOYEE s, EMPLOYEE e
    WHERE e.supervisor = s.id
    GROUP BY s.lastname
    ORDER BY 2 DESC
    ;

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    this smells too much like a homework assignment!!

    littlefoot, your solution is wrong

    okay, not wrong, but it's not right, either
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Oct 2003
    Posts
    7
    Yes..it is aprt of my assignment ..
    I am trying hard for it..
    can you give some hint??
    Thanks

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Rudy, it is impossible to produce wrong answer on Halloween

    Request was to show supervisors and number of their employees. Now this is the output:
    Code:
    SQL> select * from employee;
    
            ID LASTNAME   SUPERVISOR
    ---------- ---------- ----------
             1 menon               1
             2 seema               1
             3 brad                1
             4 chaya               3
             5 abc                 4
             6 scott               2
    
    6 rows selected.
    
    SQL> select s.lastname supervisorname, count(e.lastname) number_of_employees
      2  from employee s, employee e
      3  where e.supervisor = s.id
      4  group by s.lastname
      5  order by 2 desc;
    
    SUPERVISOR NUMBER_OF_EMPLOYEES
    ---------- -------------------
    menon                        3
    brad                         1
    seema                        1
    chaya                        1
    
    SQL>
    True, I might need new spectacles, but - do I not see obvious? Where did I go wrong?

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by Littlefoot
    True, I might need new spectacles, but - do I not see obvious? Where did I go wrong?
    I guess what Rudy is refering to is that "Menon" supervises himself, and so should not count as one of his own subordinates.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Littlefoot
    do I not see obvious? Where did I go wrong?
    and what happens if a supervisor has no employees?

    whatever you do, don't just give buddy the solution, make him go look up LEFT OUTER JOIN in the manual
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by r937
    and what happens if a supervisor has no employees?
    So everyone is a supervisor, even if they supervise no one? How egalitarian!

  9. #9
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Oh, I see! Thank you for the tips, both of you!

  10. #10
    Join Date
    Oct 2003
    Posts
    7
    Thanks for all ur discussion..I will read about the left outer join and try it again
    Thanks again

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, what you are looking for is HAVING COUNT(e.supervisor) > 0
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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