Joining 3 tables

db2Noob · #1 (**permalink**) 08-13-10, 11:22

Hi, I have the following situation:

I have 3 tables, I need all values of t1, in t2 I need certain values of one of its columns, and the t3 because I need one of its columns and because it has a column that will determine which values of t2 matches those of t1.

Here's an example:

Code:

t1
col1  col2  col3
1      a      blah1
2      b      blah2
3      c      blah3
4      d      blah4

Code:

Code:

t3
col1  col2  col3
5      a      0
10    e      9
15    f      0
20    d      9
25    b      9
30    c      9

I'd like to get all t1 + t2.col2 + t3.col3 only for those values in t2.col1=t3.col1 when t1.col2=t3.col2.

The result above should be as follows:
resulting table

Code:

t1.col1  t1.col2  t1.col3  t2.col2  t3.col3
1          a         blah1     w         0
2          b         blah2     u          9
3          c         blah3     v          9
4          d         blah4     z          9

This is the query I came up with but it throws repeated values sometimes and t2.col2 not always matches the row it's supposed to be in:

Code:

SELECT t1.col1, t1.col2, t1.col3, t2.col2, t3.col3
FROM Table1 t1 LEFT OUTER JOIN Table3 t3 ON t1.col2=t3.col2, Table2 t2
WHERE t2.col1=t3.col1

How can I improve this query to get the desired result?

Thanks a lot

ARWinner · #2 (**permalink**) 08-13-10, 12:03

Try:

Code:

SELECT t1.col1, t1.col2, t1.col3, t2.col2, t3.col3
FROM Table1 t1 
LEFT OUTER JOIN Table3 t3 ON t1.col2=t3.col2 
left outer join Table2 t2 on (t2.col1 = t3.col1)

Andy

Internet Services

Web Development

Digital Marketing

Consumer Tech