6 Replies Latest reply: Apr 5, 2013 4:49 AM by user648773 RSS

    Full Outer join question

    806711
      I have the below tables...

      Table1
      -------
      EMPID DEPID C1 C2 SUM
      ---------------------------
      100 200 2 3 111
      100 200 4 4 222
      101 201 3 6 333
      102 202 7 8 555

      Table2
      -------
      EMPID DEPID C1 C2 SUM
      ---------------------------
      100 200 4 4 333
      100 200 5 6 444
      102 202 7 9 666



      Result I am looking for ---

      Table1
      -------
      EMPID DEPID T1.C1 T1.C2 T2.C1 T2.C2 T1.SUM T2.SUM
      ---------------------------------------------------------------------------------------------
      100 200 2 3 NULL NULL 111 NULL
      100 200 4 4 4 4 222 333
      100 200 NULL NULL 5 6 NULL 444
      102 202 7 8 NULL NULL 555 NULL
      102 202 NULL NULL 7 9 NULL 666

      Any help is much appriciated. I used Full Outer Join but could not achive this.
      I am looking for a INNER JOIN for T1.EMP_ID = T2.EMP_ID and T1.DEP_ID = T2.DEP_ID and a OUTER JOIN for T1.C1 = T2.C1 and T1.C2 = T2.C2 .... So i need only thse records for which T1.EMP_ID = T2.EMP_ID and T1.DEP_ID = T2.DEP_ID always satisfy..
        • 1. Re: Full Outer join question
          sb92075
          How do I ask a question on the forums?
          SQL and PL/SQL FAQ
          • 2. Re: Full Outer join question
            chris227
            Ah, now i see
            select
              nvl(t1.empid,t2.empid) empid
            , nvl(t1.depid,t2.depid) depid
            , t1.c1 
            , t1.c2 
            , t2.c1 
            , t2.c2 
            , t1.sum
            , t2.sum
            from
            table1 t1
            full outer join
            table2 t2
            on 
            t1.empid=t2.empid
            and
            t1.depid=t2.depid
            and
            t1.c1=t2.c1
            and
            t1.c2=t2.c2
            where
            (t1.empid,t1.depid) in (select empid, depid from table2)
            or
            (t2.empid,t2.depid) in (select empid, depid from table1)
            order by 1, 2, 3, 4
            
            EMPID     DEPID     C1     C2     C1     C2     SUM     SUM
            100     200     2     3     -      -      111     -
            100     200     4     4     4     4     222     333
            100     200     -      -      5     6     -      444
            102     202     7     8     -      -      555     -
            102     202     -      -      7     9     -      666
            Dont know why you dont want row 5.
            Corrected it, best i can come up with now ...

            Edited by: chris227 on 04.04.2013 11:14
            corrected

            Edited by: chris227 on 04.04.2013 11:15
            • 3. Re: Full Outer join question
              Frank Kulash
              Hi,

              Welcome to the forum!

              Whenever you have a problem, please post a complete test scrpt that the people who want to help you can run to re-create the problem and test their ideas. Include CREATE TABLE and INSERT statements for your sample data, like this:
              CREATE TABLE     table1
              (   empid     NUMBER
              ,   depid     NUMBER
              ,   c1          NUMBER
              ,   c2          NUMBER
              ,   table1_sum     NUMBER     -- SUM is not a good column name
              );
              
              INSERT INTO table1 (empid, depid, c1, c2, table1_sum)
                              VALUES (100,   200,   2,  3,  111);
              INSERT INTO table1 (empid, depid, c1, c2, table1_sum)
                              VALUES (100,   200,   4,  4,  222);
              INSERT INTO table1 (empid, depid, c1, c2, table1_sum)
                              VALUES (101,   201,   3,  6,  333);
              INSERT INTO table1 (empid, depid, c1, c2, table1_sum)
                              VALUES (102,   202,   7,  8,  555);
              
              CREATE TABLE     table2
              (   empid     NUMBER
              ,   depid     NUMBER
              ,   c1          NUMBER
              ,   c2          NUMBER
              ,   table2_sum     NUMBER     -- SUM is not a good column name
              );
              
              INSERT INTO table2 (empid, depid, c1, c2, table2_sum)
                              VALUES (100,   200,   4,  4,  333);
              INSERT INTO table2 (empid, depid, c1, c2, table2_sum)
                              VALUES (100,   200,   5,  6,  444);
              INSERT INTO table2 (empid, depid, c1, c2, table2_sum)
                              VALUES (102,   202,   7,  9,  666);
              If looks like every row of your output will contain data either from table1 or from table2, but not from both. That sounds more like a UNION than a full outer join.

              Here's one way todo that (or perhaps two ways to do that):
              SELECT    empid
              ,        depid
              ,       c1          AS t1_c1
              ,       c2          AS t1_c2
              ,       NULL          AS t2_c1
              ,       NULL          AS t2_c2
              ,       table1_sum
              ,       NULL          AS table2_sum
              FROM       table1
              WHERE       EXISTS (
                               SELECT  0
                             FROM    table2
                             WHERE   empid     = table1.empid
                             AND     depid     = table1.depid
                           )
                   --
              UNION ALL
                   --
              SELECT    empid
              ,        depid
              ,       NULL          AS t1_c1
              ,       NULL          AS t1_c2
              ,       c1          AS t2_c1
              ,       c2          AS t2_c2
              ,       NULL          AS table1_sum
              ,       table2_sum
              FROM       table2
              WHERE       (empid, depid) IN
                            (
                               SELECT  empid, depid
                             FROM    table1
                           )
                   --
              ORDER BY  empid
              ,            depid
              ;
              I used two diferent ways to see if there was a match in the other table. I did this just to show you that there are different ways; you'll probably want to use one way or the other in both places
              In the first part of the UNION, I used an EXISTS sub-query, to see, for each row in table1, if there was a mathin row in table2.
              In the second part of the UNION, I used an IN sub-qury to find all the keys in table1, and compared each row in table2 to that list.
              One of these ways may be faster than the other for your data. I noramally use IN sub-queries for this kind of thing, and not EXISTS suib-queries.
              • 4. Re: Full Outer join question
                user648773
                Actully there is a case in which he need data from both:

                If you let me I will use the Frank query whith some changes and an aditional query:

                -- complete join
                select t1.EMPID, t1.DEPID, T1.C1, T1.C2, T2.C1, T2.C2, T1.SUM, T2.SUM
                from table1 t1,
                table2 t2
                where t1.EMPID = t2.EMPID
                     and t1.DEPID = t2.DEPID
                     and T1.C1 = T2.C1
                     and T1.C2 = T2.C2
                union all
                -- frank query with modification
                SELECT empid
                ,      depid
                ,     c1          AS t1_c1
                ,     c2          AS t1_c2
                ,     NULL          AS t2_c1
                ,     NULL          AS t2_c2
                ,     table1_sum
                ,     NULL          AS table2_sum
                FROM     table1
                WHERE     EXISTS (
                          SELECT 0
                          FROM table2
                          WHERE empid     = table1.empid
                          AND depid     = table1.depid
                          and (c1 != table1.c1 or c2!=table1.c2)
                          )
                --
                UNION ALL
                --
                SELECT empid
                ,      depid
                ,     NULL          AS t1_c1
                ,     NULL          AS t1_c2
                ,     c1          AS t2_c1
                ,     c2          AS t2_c2
                ,     NULL          AS table1_sum
                ,     table2_sum
                FROM     table2
                WHERE     EXISTS (
                          SELECT 0
                          FROM table1
                          WHERE empid     = table2.empid
                          AND depid     = table2.depid
                          and (c1 != table2.c1 or c2!=table2.c2)
                          )
                • 5. Re: Full Outer join question
                  chris227
                  user648773 wrote:
                  Actully there is a case in which he need data from both:

                  If you let me I will use the Frank query whith some changes and an aditional query:
                  Why not use the solution already given above?
                  • 6. Re: Full Outer join question
                    user648773
                    I'm sorry, yes, you are right, it seems that your solution will work fine.