This discussion is archived
3 Replies Latest reply: Jan 7, 2013 11:40 AM by Frank Kulash RSS

Display the values in a field of same table

Prasad K T Explorer
Currently Being Moderated
Hi.,

I have created table known as ess_emp_det. I have the following datas and fields,
Emp_id           Name            Emp_mgr_id 
1245        Krishna          
1246             Lalith          1245  ---> Instead of id i need to display as Krishna
1247        Rma          1245 ---> Instead of id i need to display as Krishna 
1248         Kabhul           1246  ---> Instead of id i need to display as Lalith
1249             saravan                    1247  ---> Instead of id i need to display as rma
Pls give me an idea how to get it.,

regards,
Prasad K T

Edited by: wilhelm wundt on Jan 7, 2013 11:13 AM
  • 1. Re: Display the values in a field of same table
    damorgan Oracle ACE Director
    Currently Being Moderated
    NAME is a reserved word in Oracle and should not be used to name a column. Rename the column so it has meaning. For example person_name, employee_name, student_name, etc.

    The solution to your problem is to use CONNECT BY (demos in the Oracle docs and here http://www.morganslibrary.org/reference/connectby.html)
  • 2. Re: Display the values in a field of same table
    Frank Kulash Guru
    Currently Being Moderated
    Hi, Prasad,

    If all the employees were in a table e, and all the managers were in a table m, then you would just join the tables, right?
    Well, all the employees are in a table e, and all the managers are in a table m. E and m happen to be the same table, but that doesn't change how you do the join.

    For example:
    SELECT       e.empno
    ,       e.ename
    ,       e.mgr
    ,       m.ename     AS manager_ename
    FROM           scott.emp  e
    LEFT OUTER JOIN      scott.emp  m  ON  m.empno  = e.mgr
    ORDER BY   e.empno
    ;
    Since you didn't post CREATE TABLE and INSERT statements for your table, I used scott.emp, which is very similar.
    Output:
    `    EMPNO ENAME             MGR MANAGER_EN
    ---------- ---------- ---------- ----------
          7369 SMITH            7902 FORD
          7499 ALLEN            7698 BLAKE
          7521 WARD             7698 BLAKE
          7566 JONES            7839 KING
          7654 MARTIN           7698 BLAKE
          7698 BLAKE            7839 KING
          7782 CLARK            7839 KING
          7788 SCOTT            7566 JONES
          7839 KING
          7844 TURNER           7698 BLAKE
          7876 ADAMS            7788 SCOTT
          7900 JAMES            7698 BLAKE
          7902 FORD             7566 JONES
          7934 MILLER           7782 CLARK
  • 3. Scalar Sub-Query
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    Another way is to use a scalar sub-query, like this:
    SELECT       empno
    ,       ename
    ,       mgr
    ,       (               -- Begin scalar-sub-query to get manager_ename
               SELECT  ename
               FROM    scott.emp
               WHERE   empno     = e.mgr
           ) AS manager_ename     -- End  scalar-sub-query
    FROM       scott.emp  e
    ORDER BY  e.empno
    ;
    Under some circumstances (e.g., when there are few distinct values of mgr) this can be more efficient than a self-join.
    Unlike the self-join, however, this assumes that empno is unique.



     

    I hope this answers your question.
    If not, post a little sample data (CREATE TABLE and INSERT statements) unless you want to continue using scott.emp, and also post the results you want from that data.
    Point out where the query I posted is producing the wrong results, and explain, using specific examples, how you get those results from that data.
    Always say which version of Oracle you're using (e.g., 11.2.0.2.0).
    See the forum FAQ {message:id=9360002}

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points