DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: self join with a union!!!

  1. #1
    Join Date
    Oct 2002
    Posts
    7

    Talking self join with a union!!!

    Hi All!!!

    Is there a better way of writing this query?

    select e1.EMP_NAME||' works for '||e2.EMP_NAMe
    "Employees and Their Managers"
    FROM emp_mgr e1, emp_mgr e2
    WHERE e1.EP_MGR_EMP_ID = e2.emp_ID
    union
    select emp_name ||' has no '|| nvl(to_char(EP_MGR_EMP_ID), 'Manager')
    from emp_mgr
    where EP_MGR_EMP_ID is null;

    The results

    Dave has no Manager
    Eddie has no Manager
    Im works for King
    John works for Im
    Ray works for Im
    Sarah works for king
    Scott works for Im
    King has no Manager

    Thanks

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Outer join would be much more efficient and easier to read. Something like:
    Code:
    select
      e1.EMP_NAME ||
      DECODE (e1.EP_MGR_EMP_ID , NULL, 'has no Manager',
              works for '||e2.EMP_NAMe) "Employees and Their Managers"
    FROM emp_mgr e1, emp_mgr e2
    WHERE e1.EP_MGR_EMP_ID = e2.emp_ID(+);
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

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


Click Here to Expand Forum to Full Width