Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2010
    Posts
    9

    Unanswered: Joining 3 tables

    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:
    t2
    col1  col2
    5      w
    10     x
    15     y
    20     z
    25     u
    30     v
    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
    Last edited by db2Noob; 08-13-10 at 12:35.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

Posting Permissions

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