SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Nov 2009
    Location
    Bangalore, India
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Actual value instead of Null in result of MySQL Left join

    I have two tables ; table1 and table 2,

    table1 has fields - f1, f2 and f3
    table2 has fields - f1, f2 and f4

    Code:
        table1                     table2
    
       f1   f2   f3              f1   f2   f4
       2    3    4               2    3     6
       5    8    7               5    8     7
       11   13   15              11   13   17
    I do a Left join for these two tables with this Query:

    Code:
    select table1.*,table.f4 from table1 left join tester on (table1.f1 = table2.f1 and table1.f2 = table2.f2 and table1.f3 = table2.f4);

    Output I am getting is

    Code:
    f1   f2   f3   f4
    2    3    4    null
    5    8    7    7
    11  13   15   null

    But the Output I want is

    Code:
    f1   f2   f3   f4
    2    3    4    6
    5    8    7    7
    11  13   15   17
    Please help me on how this can be got.
    Thank You
    Known is a Drop, Unknown is an Ocean

  2. #2
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,000
    Mentioned
    101 Post(s)
    Tagged
    0 Thread(s)
    Code SQL:
    SELECT
        table1.*
        , table2.f4
    FROM
        table1
    LEFT JOIN
        table2
    ON
        table1.f1 = table2.f1
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  3. #3
    SitePoint Enthusiast
    Join Date
    Nov 2009
    Location
    Bangalore, India
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by SpacePhoenix View Post
    Code SQL:
    SELECT
        table1.*
        , table2.f4
    FROM
        table1
    LEFT JOIN
        table2
    ON
        table1.f1 = table2.f1
    Thanks for getting back. But the Query that you have suggested does not provide me the result that I require. And just to inform you again, I have f1, f2 as well as the f3 columns in table1 used in the where clause.
    Thank You
    Known is a Drop, Unknown is an Ocean

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,217
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by vigneshr35 View Post
    But the Query that you have suggested does not provide me the result that I require.
    the query he provided gives exactly the result set you said you wanted

    here, you may borrow this code and test it for yourself --
    Code:
    CREATE TABLE table1
    ( f1 INTEGER 
    , f2 INTEGER 
    , f3 INTEGER 
    );
    INSERT INTO table1 VALUES 
     (  2  , 3  , 4  )                        
    ,(  5  , 8  , 7  )  
    ,(  11 , 13 , 15 )  
    ;
    CREATE TABLE table2
    ( f1 INTEGER 
    , f2 INTEGER 
    , f4 INTEGER 
    );
    INSERT INTO table2 VALUES            
     (  2  , 3  , 6  )    
    ,(  5  , 8  , 7  )    
    ,(  11 , 13 , 17 )    
    ;
    SELECT table1.*  
         , table2.f4
      FROM table1
    LEFT OUTER
      JOIN table2
        ON table2.f1 = table1.f1
    ;
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru
    Join Date
    Nov 2003
    Location
    Huntsville AL
    Posts
    689
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by SpacePhoenix View Post
    Code SQL:
    SELECT
        table1.*
        , table2.f4
    FROM
        table1
    LEFT JOIN
        table2
    ON
        table1.f1 = table2.f1 AND
        table1.f2 = table2.f2
    In your original query you were also trying to join table1.f3 and table2.f4. Remember that in a left join you always get all the records from the left side of the query. You only get values from the right side if all the join conditions are met.


Bookmarks

Posting Permissions

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