Post reply

LEFT JOIN problem

  • November 5, 2009 at 5:04 pm

    #227709

    I have 3 tables:

    test1

    id name1

    1 value1

    test2

    id name2

    1 value2

    test3

    id name3

    3 value3_01

    4 value3_02

    I want to display all records of table test1 with values of table test2 (test1.id = test2.id) and values of test3 (if test.id match with test1.id) - in this case test3 has no common ids with test1 so NULLs are displayed.

    How to make sql query to display:

    id1 name1 name2 name3

    1 value1 value2 NULL

    I constructed query:

    SELECT t1.id as id1, t1.name1 as name1, t2.name2 as name2, t3.name3 as name3

    FROM (test1 t1) LEFT JOIN (test3 t3, test2 t2)

    ON (t1.id=t2.id and t1.id=t3.id)

    but it gives me:

    id1 name1 name2 name3

    1 value1 NULL NULL

    name2 is NULL instead of desired "value2". WHY?

    LEFT JOIN DEFINITION:

    SQL LEFT JOIN Keyword

    The LEFT JOIN keyword returns all rows from the left table, even if there are no matches in the right table.

    There are no matches in test3 table when joining test1 table so name3 from table3 is NULL in result.

    BUT There are matches in table test2 (test2.id=1 and test1.id=1) so why null is displayed?

    Hope anyone can help me. I am in big trouble.

    Thanks in advace.

    Tom

  • November 5, 2009 at 8:04 pm

    #1075680

    You didn't LEFT JOIN properly. It should be...

    SELECT t1.id as id1, t1.name1 as name1, t2.name2 as name2, t3.name3 as name3

    FROM test1 t1

    LEFT JOIN test2 t2 ON t1.id = t2.id

    LEFT JOIN test3 t3 ON t1.id = t3.id

    To get better, quicker, fully tested answers in the future, please read and heed the article at the first URL in my signature below.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 5, 2009 at 11:55 pm

    #1075729

    Thank you so much. You're right. My left join was not correct, because doing this:

    FROM (test1 t1) LEFT JOIN (test3 t3, test2 t2)

    ON (t1.id=t2.id AND t1.id=t3.id)

    I actually forced SQL Server to retrieve results where test2.id=test3.id???

    t1.id=t2.id AND t1.id=t3.id MEANS THAT t2.id=t3.id?????

    thanks

  • November 6, 2009 at 12:31 am

    #1075735

    pc131 (11/5/2009)

    Thank you so much. You're right. My left join was not correct, because doing this:

    FROM (test1 t1) LEFT JOIN (test3 t3, test2 t2)

    ON (t1.id=t2.id AND t1.id=t3.id)

    I actually forced SQL Server to retrieve results where test2.id=test3.id???

    t1.id=t2.id AND t1.id=t3.id MEANS THAT t2.id=t3.id?????

    thanks

    First things first, does it work on ss2k5? i dont think so. May be you've set the compatibility mode to 80? I aint sure!

    Here you are using two types of syntax, (bold one is SQL 92, underlined one is sql 89)

    FROM (test1 t1) LEFT JOIN (test3 t3, test2 t2)[/b]

    ON (t1.id=t2.id AND t1.id=t3.id)

    sql89 is not supported on ss2k5 unless you have set the compatibility mode to 80. Thanks.

    ---------------------------------------------------------------------------------

  • November 6, 2009 at 12:52 am

    #1075741

    Nabha (11/6/2009)

    sql89 is not supported on ss2k5 unless you have set the compatibility mode to 80.

    Partly correct...SQL89 INNER JOINs are still supported, so this is valid

    SELECT t1.id as id1, t1.name1 as name1, t2.name2 as name2, t3.name3 as name3

    FROM test1 t1, test3 t3, test2 t2

    WHERE t1.id = t2.id

    AND t2.id = t3.id

    But SQL89 OUTER JOINs aren't, so this fails

    SELECT t1.id as id1, t1.name1 as name1, t2.name2 as name2, t3.name3 as name3

    FROM test1 t1, test3 t3, test2 t2

    WHERE t1.id *= t2.id

    AND t2.id *= t3.id

  • November 6, 2009 at 3:52 am

    #1075801

    Hi

    Actually it was MySQL 5 issue, but I was looking for high traffic forums, so i put it here.

    Thanks

  • November 6, 2009 at 9:50 pm

    #1076230

    pc131 (11/6/2009)

    Hi

    Actually it was MySQL 5 issue, but I was looking for high traffic forums, so i put it here.

    Thanks

    Heh... that's like standing in traffic to see if you'll get noticed. SQL <> SQL. 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply