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

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(+);

---