2 Replies Latest reply: Feb 6, 2013 5:43 PM by Frank Kulash RSS

    Equi Join and Outer join using outer keyword

    622930
      Hi,

      First lets take the create statment for scott schema.
      create table scott.emp_details(empno number, bonus_date date);
      
      Insert Into Scott.Emp_Details Values(7369, To_Date('01-jan-2013'));
      Insert Into Scott.Emp_Details Values(7499, To_Date('05-jan-2013'));
      Insert Into Scott.Emp_Details Values(7521, To_Date('10-jan-2013'));
      Insert Into Scott.Emp_Details Values(7566, To_Date('01-feb-2013'));
      Insert Into Scott.Emp_Details Values(7654, To_Date('05-feb-2013'));
      commit;
      lets also consider the basic scott.emp and scott.dept tables

      Now I would like to equi join emp table deptno col with dept table deptno col and left outer join emp table hiredate with emp_details bonus_date and empno col in emp_details can be joined(Equi Join) with empno col of emp table if needed .The outer join has to be placed using the keyword (left/right)outer join

      The select statement can have all the detials of emp table .The requirement may look weird but we have some such requirement.

      Please suggest
        • 1. Re: Equi Join and Outer join using outer keyword
          Frank Kulash
          Hi,
          sri wrote:
          Hi,

          First lets take the create statment for scott schema.
          create table scott.emp_details(empno number, bonus_date date);
          
          Insert Into Scott.Emp_Details Values(7369, To_Date('01-jan-2013'));
          Insert Into Scott.Emp_Details Values(7499, To_Date('05-jan-2013'));
          Insert Into Scott.Emp_Details Values(7521, To_Date('10-jan-2013'));
          Insert Into Scott.Emp_Details Values(7566, To_Date('01-feb-2013'));
          Insert Into Scott.Emp_Details Values(7654, To_Date('05-feb-2013'));
          commit;
          It's best not to create your own tables in Oracle-supplied schemas, such as SCOTT. Use your own schema for your own tables.
          lets also consider the basic scott.emp and scott.dept tables
          I see; you're using the standard scott,emp and scott.dept tables, plus the emp_details table you posted above.
          Now I would like to equi join emp table deptno col with dept table deptno col and left outer join emp table hiredate with emp_details bonus_date and empno col in emp_details can be joined(Equi Join) with empno col of emp table if needed .The outer join has to be placed using the keyword (left/right)outer join

          The select statement can have all the detials of emp table .The requirement may look weird but we have some such requirement.

          Please suggest
          Thanks for posting the sample data. Don't forget to post the exact output you want from that sample data.
          Do you want something like this?
          `    EMPNO ENAME          DEPTNO DNAME          BONUS_DAT
          ---------- ---------- ---------- -------------- ---------
                7369 SMITH              20 RESEARCH       01-JAN-13
                7499 ALLEN              30 SALES          05-JAN-13
                7521 WARD               30 SALES          10-JAN-13
                7566 JONES              20 RESEARCH       01-FEB-13
                7654 MARTIN             30 SALES          05-FEB-13
                7698 BLAKE              30 SALES
                7782 CLARK              10 ACCOUNTING
                7788 SCOTT              20 RESEARCH
                7839 KING               10 ACCOUNTING
                7844 TURNER             30 SALES
                7876 ADAMS              20 RESEARCH
                7900 JAMES              30 SALES
                7902 FORD               20 RESEARCH
                7934 MILLER             10 ACCOUNTING
                                        40 OPERATIONS
          If so, here's one way to do it:
          SELECT       e.empno, e.ename     -- or whatever columns you want
          ,       d.deptno, d.dname     -- or whatever columns you want
          ,       ed.bonus_date
          FROM           scott.dept  d
          LEFT OUTER JOIN      scott.emp   e   ON  e.deptno     = d.deptno
          LEFT OUTER JOIN      emp_details ed      ON  ed.empno     = e.empno
          ORDER BY  e.empno
          ;
          • 2. Re: Equi Join and Outer join using outer keyword
            odie_63
            Now I would like to equi join emp table deptno col with dept table deptno col and left outer join emp table hiredate with emp_details bonus_date and empno col in emp_details can be joined(Equi Join) with empno col of emp table if needed .The outer join has to be placed using the keyword (left/right)outer join

            The select statement can have all the detials of emp table .The requirement may look weird but we have some such requirement.
            The requirement is not weird but sounds very much like an homework assignment.
            Please suggest
            What have you tried so far?

            Edit:
            Oh well... Frank has already replied.
            If you like being spoon-fed, so be it.

            Edited by: odie_63 on 6 févr. 2013 22:27