Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2011
    Posts
    2

    Question Unanswered: Joining three same columns from three different tables

    Hi,

    I have the column "IPAddress" in three different tables, say, T1, T2 and T3. I want to take each IPAdress from each table and find out if it is present in other two tables.

    For Example,
    9.9.9.9 is present in T1 & T3.
    9.1.1.1 is present only in T3.
    So, the result should be,

    Column1 Column2 Column3
    9.9.9.9 null 9.9.9.9
    null null 9.1.1.1

    How do i join three tables???????

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You may want to use FULL OUTER JOIN, like...
    Code:
    ...
      FROM
           T1
      FULL OUTER JOIN
           T2
       ON  t2.IPAddress = t1.IPAddress
      FULL OUTER JOIN
           T3
       ON  t3.IPAddress = COALESCE(t1.IPAddress , t2.IPAddress)
    ...
    Last edited by tonkuma; 04-14-11 at 05:07.

  3. #3
    Join Date
    Apr 2011
    Posts
    2

    Red face It seems not working

    Hi Tonkuma,

    Thanks a lot for your reply. It seems not working. It says "syntax error near unexpected token `(' ".

    I don't know if the coalesce() function can be used to the right side of any operator ( especially "equal to" operator)

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What DB2 version(and platform OS) are you using?

    What is the exact query statement you issued?
    (copy and paste, if it was less than about 100 lines.)

    What error message(s)(error code and text) did you recieived?
    (copy and paste)

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You can use COALESCE function for ON condition.

    In fact, COALESCE and CAST are the only functions usable in ON condition for FULL OUTER JOIN, on DB2 for z/OS.
    (DB2 for LUW and DB2 for iSeries are more flexible.)

    Here is a copy from
    IBM Information Management Software for z/OS Solutions Information Center
    Code:
    join-condition
    
    ...
    ...
    
    For FULL OUTER joins:
    
       .-AND-------------------------------------------.
       V                                               |
    >>---full-join-expression--=--full-join-expression-+-----------><
     
     
    
    full-join-expression:
    
    >>-+-+-column-name--------+---------------------------------------------+-><
       | |               (1)  |                                             |
       | '-cast-function------'                                             |
       |                                    .---------------------------.   |
       |                                    V                           |   |
       '-COALESCE--(-+-column-name--------+---+-,--column-name--------+-+-)-'
                     |               (1)  |   |                  (1)  |
                     '-cast-function------'   '-,--cast-function------'
     
    Notes:
     1. cast-function must only contain a column
        and the casting data type must be a distinct type
         or the data type upon which the distinct type was based.
    Last edited by tonkuma; 04-19-11 at 04:39.

Tags for this Thread

Posting Permissions

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