Left Outer Join

  • Hi,

    I am using left outer join on two tables,i allways get the null values from second table even though match exists

    SELECT a.IDNo,b.IDNO,a.Name,b.Name

    from table1 a Left Outer Join table2 b

    on

    a.id1=b.id1

    and a.lid1=b.lid2

    and a.lid3=b.lid3

    Please suggest on how to resolve this

    Thank you

  • Seems to work fine for me:

    DECLARE @Table1

    TABLE

    (

    idno INT NULL,

    id1 INT NULL,

    lid1 INT NULL,

    lid3 INT NULL,

    name VARCHAR(20) NULL

    );

    DECLARE @Table2

    TABLE

    (

    idno INT NULL,

    id1 INT NULL,

    lid1 INT NULL,

    lid2 INT NULL,

    lid3 INT NULL,

    name VARCHAR(20) NULL

    );

    INSERT @Table1

    (idno, id1, lid1, lid3, name)

    VALUES (0, 1, 2, 3, 'Table 1');

    INSERT @Table2

    (idno, id1, lid1, lid2, lid3, name)

    VALUES (0, 1, 0, 2, 3, 'Table 2');

    SELECT a.IDNo,

    b.IDNO,

    a.Name,

    b.Name

    FROM @Table1 a

    LEFT

    OUTER

    JOIN @Table2 b

    ON (

    a.id1 = b.id1

    AND a.lid1 = b.lid2

    AND a.lid3 = b.lid3

    );

  • If Paul's Script does not work for you, please post sample data and table structures so we can better match the problem with an answer.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • CirquedeSQLeil (1/12/2010)


    If Paul's Script does not work for you, please post sample data and table structures so we can better match the problem with an answer.

    Quite so. In so far as it is possible to post sarcastic SQL code, that's what I was aiming for with my original post...;-)

  • Hi there,

    The script provided works but in my case the table 2 is generated and contains data which is a result of other joins

    so when i have a left outer join as above i am getting null resulted values from table2

    but when i do a inner join there exists match and returns the rows

    Please suggest on how to resolve this

    My table structure are very big with large quantinty of data so couldn't make a post

    Thanks

  • forum member (1/13/2010)


    My table structure are very big with large quantinty of data so couldn't make a post.

    There is an error in your code.

    Without seeing what you can see (even a simplified example!?) it's difficult to see how to help...?

  • Paul White (1/13/2010)


    forum member (1/13/2010)


    My table structure are very big with large quantinty of data so couldn't make a post.

    There is an error in your code.

    Without seeing what you can see (even a simplified example!?) it's difficult to see how to help...?

    Big table structure can be handled. As far as the data, just post some of the data that you know represents the issue (10 records maybe that are returned by both inner and left join).

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Paul, I giggled when I saw that 🙂

    To the original poster, the logic in the where clause is a bit strange but then we don't know your data 🙂

    a.id1=b.id1

    and a.lid1=b.lid2

    and a.lid3=b.lid3

    lid1 = lid2 sounds like it could have potential for an issue. However If you are joining non int or decimal types such as char and nchar, it is quite possible there are leading or trailing spaces on one table that match. For instance sql server wont match ' bob' = 'bob ' in that join type.

    Also if you have 'massive' amounts of data, when you are using a left join, all records from the left table are returned and only matching records contain non null data. If only a few records match and you try to eyeball across a million rows, you may not see that non null data. Whereas the inner join you could be returning the 10 records only that actually do match which makes it seem like its only working on the Inner join.

    Anyways my 2 cents to help out without data or data structures 😀

    Link to my blog http://notyelf.com/

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

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