3 Replies Latest reply on Jan 7, 2013 7:40 PM by Frank Kulash

    Display the values in a field of same table

    Prasad K T

      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.,

      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
          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
            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.
            `    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

              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.,
              See the forum FAQ {message:id=9360002}