Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2003
    Posts
    1,605

    Unanswered: SQL: joining single table with full other join problem

    Hi,
    using DB2 v9.5 on Linux I would like to write an SQL to get matematical difference in quantity between two pars of IDs (columns A and B) in bellow sample.

    I have created two samples. First sample works fine, but the second (SQL in my production) does not return desired results. The only difference I can see is first sample uses two tables and second sample uses only one table (self-referencing).

    I would like to write such an SQL for sample 2 to get the same result as SQL sample 1 returns.
    Thanks

    SAMPLE 1:
    Code:
    DROP TABLE ADMIN.T1;
    DROP TABLE ADMIN.T2;
    DROP TABLE ADMIN.T3;
    
    CREATE TABLE ADMIN.T1 (A INT, B INT, QUANTITY INT);
    CREATE TABLE ADMIN.T2 (A INT, B INT, QUANTITY INT);
    INSERT INTO ADMIN.T1 VALUES (1, 1, 100);
    INSERT INTO ADMIN.T1 VALUES (1, 2, 200);
    INSERT INTO ADMIN.T2 VALUES (1, 1, 300);
    INSERT INTO ADMIN.T2 VALUES (2, 1, 400);
    
    SELECT * FROM ADMIN.T1;
    SELECT * FROM ADMIN.T2;
    
    SELECT
        COALESCE(T1.A, T2.A) AS A,
        COALESCE(T1.B, T2.B) AS B,
        COALESCE(T1.QUANTITY,0) - COALESCE(T2.QUANTITY,0) AS C
    FROM
        ADMIN.T1 AS T1
        FULL OUTER JOIN
        ADMIN.T2 AS T2
        ON T1.A=T2.A AND T1.B=T2.B
    ;
    Above selects from sample 1 returns:
    Code:
    TABLE: T1
    A           B           QUANTITY
    ----------- ----------- -----------
              1           1         100
              1           2         200
    
    TABLE: T2
    A           B           QUANTITY
    ----------- ----------- -----------
              1           1         300
              2           1         400
    
    DESIRED RESULT
    A           B           C
    ----------- ----------- -----------
              1           1        -200
              2           1        -400
              1           2         200
    SAMPLE 2:
    Code:
    CREATE TABLE ADMIN.T3 (TABLE CHAR(2), A INT, B INT, QUANTITY INT);
    INSERT INTO ADMIN.T3 VALUES ('T1', 1, 1, 100);
    INSERT INTO ADMIN.T3 VALUES ('T1', 1, 2, 200);
    INSERT INTO ADMIN.T3 VALUES ('T2', 1, 1, 300);
    INSERT INTO ADMIN.T3 VALUES ('T2', 2, 1, 400);
    
    SELECT * FROM ADMIN.T3;
    
    SELECT
        COALESCE(T1.A, T2.A) AS A,
        COALESCE(T1.B, T2.B) AS B,
        COALESCE(T1.QUANTITY,0) - COALESCE(T2.QUANTITY,0) AS QUANTITY
    FROM
        ADMIN.T3 AS T1
        FULL OUTER JOIN
        ADMIN.T3 AS T2
        ON T1.A=T2.A AND T1.B=T2.B
    WHERE
        T1.TABLE='T1'
        AND
        T2.TABLE='T2'
    ;
    Above selects from sample 2 returns:
    Code:
    TABLE: T3
    TABLE A           B           QUANTITY
    ----- ----------- ----------- -----------
    T1              1           1         100
    T1              1           2         200
    T2              1           1         300
    T2              2           1         400
    
    UNEXPECTED RESULT
    A           B           QUANTITY
    ----------- ----------- -----------
              1           1        -200
    Why SQL from sample 2 returns different result than SQL from sample 1? How to write such an SQL for sample 2 to get the same result as in sample1?

  2. #2
    Join Date
    Jan 2003
    Posts
    1,605
    I have solved the problem (see bellow). Just wondering is there any other (simpler) way to get the same result?
    Code:
    SELECT
        COALESCE(T1.A, T2.A),
        COALESCE(T1.B, T2.B),
        COALESCE(T1.QUANTITY, 0) - COALESCE(T2.QUANTITY, 0)
    FROM
    (
    SELECT
        A,
        B,
        QUANTITY
    FROM
        ADMIN.T3
    WHERE
        TABLE='T1'
    ) AS T1
    FULL OUTER JOIN
    (
    SELECT
        A,
        B,
        QUANTITY
    FROM
        ADMIN.T3
    WHERE
        TABLE='T2'
    ) AS T2
    ON T1.A=T2.A AND T1.B=T2.B

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Another way may be...

    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT
           A
         , B
         , SUM( CASE table
                WHEN 'T1' THEN
                     + QUANTITY
                ELSE - QUANTITY
                END
              ) AS QUANTITY
     FROM  ADMIN.T3
     GROUP BY
           B
         , A
    ;
    ------------------------------------------------------------------------------
    
    A           B           QUANTITY   
    ----------- ----------- -----------
              1           1        -200
              2           1        -400
              1           2         200
    
      3 record(s) selected.

  4. #4
    Join Date
    Nov 2011
    Posts
    334
    I think the sample 2 :
    Code:
    SELECT
        COALESCE(T1.A, T2.A) AS A,
        COALESCE(T1.B, T2.B) AS B,
        COALESCE(T1.QUANTITY,0) - COALESCE(T2.QUANTITY,0) AS QUANTITY
    FROM
        ADMIN.T3 AS T1
        FULL OUTER JOIN
        ADMIN.T3 AS T2
        ON T1.A=T2.A AND T1.B=T2.B
    WHERE
        T1.TABLE='T1'
        AND
        T2.TABLE='T2';
    is equal to
    Code:
    SELECT
        COALESCE(T1.A, T2.A) AS A,
        COALESCE(T1.B, T2.B) AS B,
        COALESCE(T1.QUANTITY,0) - COALESCE(T2.QUANTITY,0) AS QUANTITY
    FROM
        ADMIN.T3 AS T1
        INNER JOIN
        ADMIN.T3 AS T2
        WHERE T1.A=T2.A AND T1.B=T2.B
        AND
        T1.TABLE='T1'
        AND
        T2.TABLE='T2';
    because there are local predicate on table t1 and t2。
    So it is not strange that the result has the only one matched row.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If you want to use FULL OUTER JOIN, here is another example.

    Example 3:
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT
           COALESCE(T1.A, T2.A) AS A
         , COALESCE(T1.B, T2.B) AS B
         , COALESCE(T1.QUANTITY,0) - COALESCE(T2.QUANTITY,0) AS QUANTITY
         , t1.table , t2.table
     FROM
           ADMIN.T3 AS T1
     FULL  OUTER JOIN
           ADMIN.T3 AS T2
      ON
           t1.table = 'T1'
       AND t2.table = 'T2'
       AND T1.A = T2.A
       AND T1.B = T2.B
     WHERE (t1.table = 'T1' or t1.table IS NULL)
       AND (t2.table = 'T2' or t2.table IS NULL)
     ORDER BY
           B
         , A
    ;
    ------------------------------------------------------------------------------
    
    A           B           QUANTITY    TABLE TABLE
    ----------- ----------- ----------- ----- -----
              1           1        -200 T1    T2   
              2           1        -400 -     T2   
              1           2         200 T1    -    
    
      3 record(s) selected.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Rationale of Example 3.

    (1) See which rows of T1 joined with which rows of T2, before applying WHERE clause.

    Note: data types were altered and some selected columns were formatted, for ease of looking.
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT
           COALESCE(T1.A, T2.A) AS A
         , COALESCE(T1.B, T2.B) AS B
         , COALESCE(T1.QUANTITY,0) - COALESCE(T2.QUANTITY,0) AS QUANTITY
         , '    ' || t1.table AS t1_tbl
         , t1.a AS t1_a , t1.b AS t1_b
         , '    ' || t2.table AS t2_tbl
         , t2.a AS t2_a , t2.b AS t2_b
     FROM
           ADMIN.T3 AS T1
     FULL  OUTER JOIN
           ADMIN.T3 AS T2
      ON
           t1.table = 'T1'
       AND t2.table = 'T2'
       AND
           T1.A = T2.A
       AND T1.B = T2.B
    /*
     WHERE (t1.table = 'T1' or t1.table IS NULL)
       AND (t2.table = 'T2' or t2.table IS NULL)
    */
     ORDER BY
           t1.table
         , t1.a , t1.b
         , t2.table
         , t2.a , t2.b
    ;
    ------------------------------------------------------------------------------
    
    A      B      QUANTITY    T1_TBL T1_A   T1_B   T2_TBL T2_A   T2_B  
    ------ ------ ----------- ------ ------ ------ ------ ------ ------
         1      1        -200     T1      1      1     T2      1      1
         1      2         200     T1      1      2 -           -      -
         1      1         300     T2      1      1 -           -      -
         2      1         400     T2      2      1 -           -      -
         1      1        -100 -           -      -     T1      1      1
         1      2        -200 -           -      -     T1      1      2
         2      1        -400 -           -      -     T2      2      1
    
      7 record(s) selected.
    Note(a): Generally speaking, FULL OUTER JOIN is equivalent to (JEFT OUTER JOIN) UNION (RIGHT OUTER JOIN).
    Though, actual syntax needs more elements, like SELECT, FROM, and ON.

    Note(b): In OUTER JOIN, all rows of outer table are selected, even if some ON conditions for outer table were not satisfied.
    ON conditions used to select joining rows of inner table for each rows of outer table.


    (2) Apply WHERE clause to the result of (1).
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT
           COALESCE(T1.A, T2.A) AS A
         , COALESCE(T1.B, T2.B) AS B
         , COALESCE(T1.QUANTITY,0) - COALESCE(T2.QUANTITY,0) AS QUANTITY
         , '    ' || t1.table AS t1_tbl
         , t1.a AS t1_a , t1.b AS t1_b
         , '    ' || t2.table AS t2_tbl
         , t2.a AS t2_a , t2.b AS t2_b
     FROM
           ADMIN.T3 AS T1
     FULL  OUTER JOIN
           ADMIN.T3 AS T2
      ON
           t1.table = 'T1'
       AND t2.table = 'T2'
       AND
           T1.A = T2.A
       AND T1.B = T2.B
    
     WHERE (t1.table = 'T1' or t1.table IS NULL)
       AND (t2.table = 'T2' or t2.table IS NULL)
    
     ORDER BY
           t1.table
         , t1.a , t1.b
         , t2.table
         , t2.a , t2.b
    ;
    ------------------------------------------------------------------------------
    
    A      B      QUANTITY    T1_TBL T1_A   T1_B   T2_TBL T2_A   T2_B  
    ------ ------ ----------- ------ ------ ------ ------ ------ ------
         1      1        -200     T1      1      1     T2      1      1
         1      2         200     T1      1      2 -           -      -
         2      1        -400 -           -      -     T2      2      1
    
      3 record(s) selected.

    (3) If the predicates " t1.table = 'T1' AND t2.table = 'T2' " were not specified,
    at least same row in T1 and T2 joined in the FULL OUTER JOIN,
    then the FULL OUTER JOIN worked same as INNER JOIN.
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT
           COALESCE(T1.A, T2.A) AS A
         , COALESCE(T1.B, T2.B) AS B
         , COALESCE(T1.QUANTITY,0) - COALESCE(T2.QUANTITY,0) AS QUANTITY
         , '    ' || t1.table AS t1_tbl
         , t1.a AS t1_a , t1.b AS t1_b
         , '    ' || t2.table AS t2_tbl
         , t2.a AS t2_a , t2.b AS t2_b
     FROM
           ADMIN.T3 AS T1
     FULL  OUTER JOIN
           ADMIN.T3 AS T2
      ON
    /*
           t1.table = 'T1'
       AND t2.table = 'T2'
       AND
    */
           T1.A = T2.A
       AND T1.B = T2.B
    /*
     WHERE (t1.table = 'T1' or t1.table IS NULL)
       AND (t2.table = 'T2' or t2.table IS NULL)
    */
     ORDER BY
           t1.table
         , t1.a , t1.b
         , t2.table
         , t2.a , t2.b
    ;
    ------------------------------------------------------------------------------
    
    A      B      QUANTITY    T1_TBL T1_A   T1_B   T2_TBL T2_A   T2_B  
    ------ ------ ----------- ------ ------ ------ ------ ------ ------
         1      1           0     T1      1      1     T1      1      1
         1      1        -200     T1      1      1     T2      1      1
         1      2           0     T1      1      2     T1      1      2
         1      1         200     T2      1      1     T1      1      1
         1      1           0     T2      1      1     T2      1      1
         2      1           0     T2      2      1     T2      2      1
    
      6 record(s) selected.
    =

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    A small variation of Example 3.

    Example 4:
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT
           COALESCE(T1.A, T2.A) AS A
         , COALESCE(T1.B, T2.B) AS B
         , COALESCE(T1.QUANTITY,0) - COALESCE(T2.QUANTITY,0) AS QUANTITY
         , '    ' || t1.table AS t1_tbl
         , t1.a AS t1_a , t1.b AS t1_b
         , '    ' || t2.table AS t2_tbl
         , t2.a AS t2_a , t2.b AS t2_b
     FROM
           ADMIN.T3 AS T1
     FULL  OUTER JOIN
           ADMIN.T3 AS T2
      ON
           t1.table < t2.table
       AND
           T1.A = T2.A
       AND T1.B = T2.B
    
     WHERE COALESCE(t1.table , 'T1') = 'T1'
       AND COALESCE(t2.table , 'T2') = 'T2'
    ;
    ------------------------------------------------------------------------------
    
    A      B      QUANTITY    T1_TBL T1_A   T1_B   T2_TBL T2_A   T2_B  
    ------ ------ ----------- ------ ------ ------ ------ ------ ------
         1      1        -200     T1      1      1     T2      1      1
         1      2         200     T1      1      2 -           -      -
         2      1        -400 -           -      -     T2      2      1
    
      3 record(s) selected.

    Intermediate result before applying WHERE conditions.
    (Same as intermediate result of Example 3.)
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT
           COALESCE(T1.A, T2.A) AS A
         , COALESCE(T1.B, T2.B) AS B
         , COALESCE(T1.QUANTITY,0) - COALESCE(T2.QUANTITY,0) AS QUANTITY
         , '    ' || t1.table AS t1_tbl
         , t1.a AS t1_a , t1.b AS t1_b
         , '    ' || t2.table AS t2_tbl
         , t2.a AS t2_a , t2.b AS t2_b
     FROM
           ADMIN.T3 AS T1
     FULL  OUTER JOIN
           ADMIN.T3 AS T2
      ON
           t1.table < t2.table
       AND
           T1.A = T2.A
       AND T1.B = T2.B
    /*
     WHERE COALESCE(t1.table , 'T1') = 'T1'
       AND COALESCE(t2.table , 'T2') = 'T2'
    */
    ;
    ------------------------------------------------------------------------------
    
    A      B      QUANTITY    T1_TBL T1_A   T1_B   T2_TBL T2_A   T2_B  
    ------ ------ ----------- ------ ------ ------ ------ ------ ------
         1      1        -100 -           -      -     T1      1      1
         1      2        -200 -           -      -     T1      1      2
         1      1        -200     T1      1      1     T2      1      1
         2      1        -400 -           -      -     T2      2      1
         1      2         200     T1      1      2 -           -      -
         1      1         300     T2      1      1 -           -      -
         2      1         400     T2      2      1 -           -      -
    
      7 record(s) selected.
    Last edited by tonkuma; 06-16-12 at 15:14.

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You might be prefer "t1.table <> t2.table" than "t1.table < t2.table"

    Example 4a:
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT
           COALESCE(T1.A, T2.A) AS A
         , COALESCE(T1.B, T2.B) AS B
         , COALESCE(T1.QUANTITY,0) - COALESCE(T2.QUANTITY,0) AS QUANTITY
         , '    ' || t1.table AS t1_tbl
         , t1.a AS t1_a , t1.b AS t1_b
         , '    ' || t2.table AS t2_tbl
         , t2.a AS t2_a , t2.b AS t2_b
     FROM
           ADMIN.T3 AS T1
     FULL  OUTER JOIN
           ADMIN.T3 AS T2
      ON
           t1.table <> t2.table
       AND
           T1.A = T2.A
       AND T1.B = T2.B
    
     WHERE COALESCE(t1.table , 'T1') = 'T1'
       AND COALESCE(t2.table , 'T2') = 'T2'
    ;
    ------------------------------------------------------------------------------
    
    A      B      QUANTITY    T1_TBL T1_A   T1_B   T2_TBL T2_A   T2_B  
    ------ ------ ----------- ------ ------ ------ ------ ------ ------
         1      1        -200     T1      1      1     T2      1      1
         1      2         200     T1      1      2 -           -      -
         2      1        -400 -           -      -     T2      2      1
    
      3 record(s) selected.

    Intermediate result before applying WHERE conditions.
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT
           COALESCE(T1.A, T2.A) AS A
         , COALESCE(T1.B, T2.B) AS B
         , COALESCE(T1.QUANTITY,0) - COALESCE(T2.QUANTITY,0) AS QUANTITY
         , '    ' || t1.table AS t1_tbl
         , t1.a AS t1_a , t1.b AS t1_b
         , '    ' || t2.table AS t2_tbl
         , t2.a AS t2_a , t2.b AS t2_b
     FROM
           ADMIN.T3 AS T1
     FULL  OUTER JOIN
           ADMIN.T3 AS T2
      ON
           t1.table <> t2.table
       AND
           T1.A = T2.A
       AND T1.B = T2.B
    /*
     WHERE COALESCE(t1.table , 'T1') = 'T1'
       AND COALESCE(t2.table , 'T2') = 'T2'
    */
    ;
    ------------------------------------------------------------------------------
    
    A      B      QUANTITY    T1_TBL T1_A   T1_B   T2_TBL T2_A   T2_B  
    ------ ------ ----------- ------ ------ ------ ------ ------ ------
         1      1         200     T2      1      1     T1      1      1
         1      2        -200 -           -      -     T1      1      2
         1      1        -200     T1      1      1     T2      1      1
         2      1        -400 -           -      -     T2      2      1
         1      2         200     T1      1      2 -           -      -
         2      1         400     T2      2      1 -           -      -
    
      6 record(s) selected.

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Removed, because of redundant/inefficient way.
    Last edited by tonkuma; 06-16-12 at 23:42.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •