Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2012
    Posts
    8

    Unanswered: Regarding the joins which fetchs the data of mutliple tables

    SELECT TA1.COLUMN1,TA2.COLUM2,TA3.COLUMN3
    LEFT JOIN TABLE2 TA2 ON TA1.COLUMN2 = TA2.COLUMN2
    LEFT JOIN TABLE3 TA3 ON TA2.COLUMN1 = TA3.COLUMN1 AND TA2.COLUMN2 = TA3.COLUMN2 and TA3.COLUMN3=TA2.COLUMN3
    WHERE TA2.ID=? AND TA2.CODE=? AND TA1.MAIN_ID=?
    GROUP BY TA1.COLUMN1,TA2.COLUM2,TA3.COLUMN3

    I am trying to write a sql where I have to get all the records in TABLE1 it may or may not have correspoindng record on TABLE2 and TABLE3

    I wrote the above query but its giving me the records which have some values in TABLE2 and TABLE3 .Please help

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    The problem is this line:
    Code:
    WHERE TA2.ID=? AND TA2.CODE=? AND TA1.MAIN_ID=?
    If TA2.ID is NULL or TA2.CODE is NULL, both predicates will evaluate to "unknown", which is interpreted as "false" in a where condition. You may want to use something like:
    Code:
    WHERE ( TA2.ID IS NULL OR TA2.ID=? ) AND
            ( TA2.CODE IS NULL OR TA2.CODE=? ) AND
            TA1.MAIN_ID=?
    Or you put the 2 conditions on TA2 into the join predicate. (This is not very nice, but it should get you going.)
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Another issue is that the coumns in SELECT clause are completely same as columns in GROUP bY clause, so GROUP bY clause is not necessary.

  4. #4
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by tonkuma View Post
    Another issue is that the coumns in SELECT clause are completely same as columns in GROUP bY clause, so GROUP bY clause is not necessary.
    Tonkuma, I'm not sure I understand your remark. Removing the GROUP BY clause will change the result unless there is a 1-1 relationship between the tables corresponding to the join conditions.
    --
    Lennart

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Sorry!

    Generally speaking, if removed GROUP BY clause from the query, DISTINCT might be neccesary.

    Anyway, this would be right.
    the coumns in SELECT clause are completely same as columns in GROUP bY clause, so GROUP bY clause is not necessary.
    Example 1-a and Example 1-b produced same results.

    Example 1-a: with GROUP BY
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT dp.deptno
         , dp.deptname
         , da.deptname AS admr_deptname
         , e .empno
     FROM  department dp
     LEFT  OUTER JOIN
           department da
      ON   da.deptno = dp.admrdept
     LEFT  OUTER JOIN
           employee   e
      ON   e.workdept = dp.deptno
     WHERE dp.deptno <= 'D01'
     GROUP BY
           dp.deptno
         , dp.deptname
         , da.deptname /* AS admr_deptname */
         , e .empno;
    ------------------------------------------------------------------------------
    
    DEPTNO DEPTNAME                             ADMR_DEPTNAME                        EMPNO 
    ------ ------------------------------------ ------------------------------------ ------
    A00    SPIFFY COMPUTER SERVICE DIV.         SPIFFY COMPUTER SERVICE DIV.         000110
    A00    SPIFFY COMPUTER SERVICE DIV.         SPIFFY COMPUTER SERVICE DIV.         000120
    A00    SPIFFY COMPUTER SERVICE DIV.         SPIFFY COMPUTER SERVICE DIV.         000010
    B01    PLANNING                             SPIFFY COMPUTER SERVICE DIV.         000020
    C01    INFORMATION CENTER                   SPIFFY COMPUTER SERVICE DIV.         000130
    C01    INFORMATION CENTER                   SPIFFY COMPUTER SERVICE DIV.         000030
    C01    INFORMATION CENTER                   SPIFFY COMPUTER SERVICE DIV.         000140
    D01    DEVELOPMENT CENTER                   SPIFFY COMPUTER SERVICE DIV.         -     
    
      8 record(s) selected.
    Example 1-b: without GROUP BY.
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT dp.deptno
         , dp.deptname
         , da.deptname AS admr_deptname
         , e .empno
     FROM  department dp
     LEFT  OUTER JOIN
           department da
      ON   da.deptno = dp.admrdept
     LEFT  OUTER JOIN
           employee   e
      ON   e.workdept = dp.deptno
     WHERE dp.deptno <= 'D01'
    /*
     GROUP BY
           dp.deptno
         , dp.deptname
         , da.deptname /* AS admr_deptname */
         , e .empno
    */
    ;
    ------------------------------------------------------------------------------
    
    DEPTNO DEPTNAME                             ADMR_DEPTNAME                        EMPNO 
    ------ ------------------------------------ ------------------------------------ ------
    A00    SPIFFY COMPUTER SERVICE DIV.         SPIFFY COMPUTER SERVICE DIV.         000110
    A00    SPIFFY COMPUTER SERVICE DIV.         SPIFFY COMPUTER SERVICE DIV.         000120
    A00    SPIFFY COMPUTER SERVICE DIV.         SPIFFY COMPUTER SERVICE DIV.         000010
    B01    PLANNING                             SPIFFY COMPUTER SERVICE DIV.         000020
    C01    INFORMATION CENTER                   SPIFFY COMPUTER SERVICE DIV.         000130
    C01    INFORMATION CENTER                   SPIFFY COMPUTER SERVICE DIV.         000030
    C01    INFORMATION CENTER                   SPIFFY COMPUTER SERVICE DIV.         000140
    D01    DEVELOPMENT CENTER                   SPIFFY COMPUTER SERVICE DIV.         -     
    
      8 record(s) selected.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Another example which DISTINCT is neccesary, if removed GROUP BY.

    Example 2-a and Example 2-c produced same results.
    Example 2-b produced duplicated rows.

    Example 2-a: with GROUP BY.
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT dp.deptno
         , dp.deptname
         , da.deptname AS admr_deptname
         , e .sex
     FROM  department dp
     LEFT  OUTER JOIN
           department da
      ON   da.deptno = dp.admrdept
     LEFT  OUTER JOIN
           employee   e
      ON   e.workdept = dp.deptno
     WHERE dp.deptno <= 'D01'
     GROUP BY
           dp.deptno
         , dp.deptname
         , da.deptname /* AS admr_deptname */
         , e .sex
    ;
    ------------------------------------------------------------------------------
    
    DEPTNO DEPTNAME                             ADMR_DEPTNAME                        SEX
    ------ ------------------------------------ ------------------------------------ ---
    A00    SPIFFY COMPUTER SERVICE DIV.         SPIFFY COMPUTER SERVICE DIV.         F  
    A00    SPIFFY COMPUTER SERVICE DIV.         SPIFFY COMPUTER SERVICE DIV.         M  
    B01    PLANNING                             SPIFFY COMPUTER SERVICE DIV.         M  
    C01    INFORMATION CENTER                   SPIFFY COMPUTER SERVICE DIV.         F  
    D01    DEVELOPMENT CENTER                   SPIFFY COMPUTER SERVICE DIV.         -  
    
      5 record(s) selected.
    Example 2-b: Removed GROUP BY
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT dp.deptno
         , dp.deptname
         , da.deptname AS admr_deptname
         , e .sex
     FROM  department dp
     LEFT  OUTER JOIN
           department da
      ON   da.deptno = dp.admrdept
     LEFT  OUTER JOIN
           employee   e
      ON   e.workdept = dp.deptno
     WHERE dp.deptno <= 'D01'
    /*
     GROUP BY
           dp.deptno
         , dp.deptname
         , da.deptname /* AS admr_deptname */
         , e .sex
    */
    ;
    ------------------------------------------------------------------------------
    
    DEPTNO DEPTNAME                             ADMR_DEPTNAME                        SEX
    ------ ------------------------------------ ------------------------------------ ---
    A00    SPIFFY COMPUTER SERVICE DIV.         SPIFFY COMPUTER SERVICE DIV.         M  
    A00    SPIFFY COMPUTER SERVICE DIV.         SPIFFY COMPUTER SERVICE DIV.         M  
    A00    SPIFFY COMPUTER SERVICE DIV.         SPIFFY COMPUTER SERVICE DIV.         F  
    B01    PLANNING                             SPIFFY COMPUTER SERVICE DIV.         M  
    C01    INFORMATION CENTER                   SPIFFY COMPUTER SERVICE DIV.         F  
    C01    INFORMATION CENTER                   SPIFFY COMPUTER SERVICE DIV.         F  
    C01    INFORMATION CENTER                   SPIFFY COMPUTER SERVICE DIV.         F  
    D01    DEVELOPMENT CENTER                   SPIFFY COMPUTER SERVICE DIV.         -  
    
      8 record(s) selected.
    Example 2-c: Removed GROUP BY and added DISTINCT
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT DISTINCT
           dp.deptno
         , dp.deptname
         , da.deptname AS admr_deptname
         , e .sex
     FROM  department dp
     LEFT  OUTER JOIN
           department da
      ON   da.deptno = dp.admrdept
     LEFT  OUTER JOIN
           employee   e
      ON   e.workdept = dp.deptno
     WHERE dp.deptno <= 'D01'
    /*
     GROUP BY
           dp.deptno
         , dp.deptname
         , da.deptname /* AS admr_deptname */
         , e .sex
    */
    ;
    ------------------------------------------------------------------------------
    
    DEPTNO DEPTNAME                             ADMR_DEPTNAME                        SEX
    ------ ------------------------------------ ------------------------------------ ---
    A00    SPIFFY COMPUTER SERVICE DIV.         SPIFFY COMPUTER SERVICE DIV.         F  
    A00    SPIFFY COMPUTER SERVICE DIV.         SPIFFY COMPUTER SERVICE DIV.         M  
    B01    PLANNING                             SPIFFY COMPUTER SERVICE DIV.         M  
    C01    INFORMATION CENTER                   SPIFFY COMPUTER SERVICE DIV.         F  
    D01    DEVELOPMENT CENTER                   SPIFFY COMPUTER SERVICE DIV.         -  
    
      5 record(s) selected.

Posting Permissions

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