[skadouche]

how do I get all EMPLOYEE_ID, their FULL_NAME and the FULL_NAME of their managers. If an employee has no manager, then the FULL_NAME of the manager on the row for that employee may be left blank, but that employee must still be in the listing. The column names on the listing must clearly indicate what the column represents. here is what my table looks like:


MANAGER_ID EMPLOYEE_ID FULL_NAME JOB_ID
101 200 Whalen Jennifer AD_ASST
100 201 Hartstein Michael MK_MAN
201 202 Fay Pat MK_REP
101 205 Higgins Shelley AC_MGR
205 206 Gietz William AC_ACCOUNT
100 King Steven AD_PRES
100 101 Kochhar Neena AD_VP
100 102 De Haan Lex AD_VP
102 103 Hunold Alexander IT_PROG
103 104 Ernst Bruce IT_PROG
103 107 Lorentz Diana IT_PROG
100 124 Mourgos Kevin ST_MAN
124 141 Rajs Trenna ST_CLERK
124 142 Davies Curtis ST_CLERK
124 143 Matos Randall ST_CLERK
124 144 Vargas Peter ST_CLERK
100 149 Zlotkey Eleni SA_MAN
149 174 Abel Ellen SA_REP
149 176 Taylor Jonathon SA_REP


PLEASE HELP as I cannot get this to work!

here is a better look at the table, the formating didn't show up right the fist time

Attached File(s)

•  isqlplus.html (3.84K)
  Number of downloads: 28

[CapC]

I'm going to assume a lot of things here...
A) I'm assuming you have a table where the managers full name is listed
I'm going to assume the PK of the emps table is EMPLOYEE_ID
C) The PK of the managers table is MANAGER_ID (and it is a foreign key of the emps table)

In this case:

EMPLOYEE_ID, their FULL_NAME and the FULL_NAME of their managers

SELECT e.employee_id, e.full_name e.MANAGER_ID, m.MANAGER_ID
FROM emps e, manager_table_name m
WHERE e.manager_ID = m.manager_ID
AND NVL(m.manager_ID, 0)
group by m.manager_ID
order by e.employee_id desc;

[idunno]

is this what you are looking for?

select emps.EMPLOYEE_ID ID, 
       emps.FULL_NAME NAME, 
       mgr.FULL_NAME MANAGER
from emps, 
    (select EMPLOYEE_ID,FULL_NAME from emps) mgr
WHERE emps.EMPLOYEE_ID = mgr.EMPLOYEE_ID(+)

[idunno]

Sorry its
WHERE emps.MANAGER_ID = mgr.EMPLOYEE_ID(+)