5 Replies Latest reply: Sep 25, 2012 8:25 AM by John Spencer RSS

    Doubt with Query

    799603
      Hi all, i have a doubt with the following query.
      SQL> SELECT emp.empno "Emp#",
        2         emp.ename "Employee",  
        3         m.ename "Manager",
        4         emp.sal "Salary"
        5    FROM emp 
        6   LEFT OUTER JOIN emp m 
        7    ON emp.mgr = m.empno;
      
            Emp# Employee   Manager        Salary
      ---------- ---------- ---------- ----------
            7566 JONES      KING             2975
            7782 CLARK      KING             2450
            7698 BLAKE      KING             2850
            7900 JAMES      BLAKE             950
            7844 TURNER     BLAKE            1500
            7654 MARTIN     BLAKE            1460
            7521 WARD       BLAKE            1250
            7499 ALLEN      BLAKE            1600
            7934 MILLER     CLARK            1300
            7902 FORD       JONES            3000
            7788 SCOTT      JONES            3000
            7876 ADAMS      SCOTT            1100
            7369 SMITH      FORD              800
            7839 KING                        5000
      
      14 rows selected.
      Passing bind variable
      SQL> SELECT emp.empno "Emp#",
        2         emp.ename "Employee",  
        3         m.ename "Manager",
        4         emp.sal "Salary"
        5    FROM emp 
        6   LEFT OUTER JOIN emp m 
        7    ON emp.mgr = m.empno
        8    AND emp.empno = NVL(&empno,emp.empno);
      Enter value for empno: 7566
      old   8:   AND emp.empno = NVL(&empno,emp.empno)
      new   8:   AND emp.empno = NVL(7566,emp.empno)
      
            Emp# Employee   Manager        Salary
      ---------- ---------- ---------- ----------
            7839 KING                        5000
            7698 BLAKE                       2850
            7782 CLARK                       2450
            7566 JONES      KING             2975
            7788 SCOTT                       3000
            7902 FORD                        3000
            7369 SMITH                        800
            7499 ALLEN                       1600
            7521 WARD                        1250
            7654 MARTIN                      1460
            7844 TURNER                      1500
            7876 ADAMS                       1100
            7900 JAMES                        950
            7934 MILLER                      1300
      
      14 rows selected.
      Why am i getting all the details when i need to get only the details of empno 7566.Can anyone suggest me where i am doing wrong and kindly explain me how to get only one employee details

      Thanks in advance!!
        • 1. Re: Doubt with Query
          Vivek L
          sandy wrote:
          Why am i getting all the details when i need to get only the details of empno 7566.Can anyone suggest me where i am doing wrong and kindly explain me how to get only one employee details
          because you have used outer join, which is bound to return all rows from emp and matching rows from emp (aliased by m).
          Did you notice that all rows in output other than empno 7566 have their manager column as null?
          If you want only one row, use this instead:
          SQL> ed
          Wrote file afiedt.buf
          
            1   select e.empno "emp#",
            2         e.ename "employee",
            3         m.ename "manager",
            4         e.sal   "salary"
            5  from   emp e
            6         join emp m
            7           on e.mgr = m.empno
            8* where  e.empno = nvl(&empno,e.empno)
          SQL> /
          Enter value for empno: 7566
          old   8: where  e.empno = nvl(&empno,e.empno)
          new   8: where  e.empno = nvl(7566,e.empno)
          
                emp# employee   manager        salary
          ---------- ---------- ---------- ----------
                7566 JONES      KING             2975
          
          1 row selected.
          • 2. Re: Doubt with Query
            799603
            Yes i tried this before posting here but empno 7839 doesn't turn up using join as he has no manager.So,i guess there's no other way to get all the records(14) using bind variable.
            • 3. Re: Doubt with Query
              953480
              Do not use "Left Outer Join" ,because it return all match and unmatched rows...
              use self join instead
              • 4. Re: Doubt with Query
                Gurjeet
                Do not use "Left Outer Join" ,because it return all match and unmatched rows...
                use self join instead
                • 5. Re: Doubt with Query
                  John Spencer
                  >

                  Snip
                  SQL> SELECT emp.empno "Emp#",
                  2         emp.ename "Employee",  
                  3         m.ename "Manager",
                  4         emp.sal "Salary"
                  5    FROM emp 
                  6   LEFT OUTER JOIN emp m 
                  7    ON emp.mgr = m.empno
                  8    AND emp.empno = NVL(&empno,emp.empno);
                  Enter value for empno: 7566
                  old   8:   AND emp.empno = NVL(&empno,emp.empno)
                  new   8:   AND emp.empno = NVL(7566,emp.empno)
                  
                  Emp# Employee   Manager        Salary
                  ---------- ---------- ---------- ----------
                  7839 KING                        5000
                  7698 BLAKE                       2850
                  7782 CLARK                       2450
                  7566 JONES      KING             2975
                  7788 SCOTT                       3000
                  7902 FORD                        3000
                  7369 SMITH                        800
                  7499 ALLEN                       1600
                  7521 WARD                        1250
                  7654 MARTIN                      1460
                  7844 TURNER                      1500
                  7876 ADAMS                       1100
                  7900 JAMES                        950
                  7934 MILLER                      1300
                  
                  14 rows selected.
                  Why am i getting all the details when i need to get only the details of empno 7566.Can anyone suggest me where i am doing wrong and kindly explain me how to get only one employee details

                  Thanks in advance!!
                  You are getting all of the rows because your predicate is part of the join condition, and not a predicate against the whole query. You need something more like:
                  SELECT emp.empno "Emp#",
                         emp.ename "Employee",
                         m.ename "Manager",
                         emp.sal "Salary"
                  FROM emp
                     LEFT OUTER JOIN emp m
                        ON emp.mgr = m.empno
                  WHERE emp.empno = NVL(&empno,emp.empno);
                  So the empno predicate is applied against the results of the join.

                  John