Joining three same columns from three different tables

Guru Manokar · #1 (**permalink**) 04-14-11, 02:54

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???????

tonkuma · #2 (**permalink**) 04-14-11, 04:03

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)
...

Guru Manokar · #3 (**permalink**) 04-19-11, 02:47

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)

tonkuma · #4 (**permalink**) 04-19-11, 03:15

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)

tonkuma · #5 (**permalink**) 04-19-11, 03:33

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech