InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 20:34:16 GMT20http://www.sqlservercentral.com/Forums/Topic846659-338-1.aspxPaul, 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.id1and a.lid1=b.lid2and a.lid3=b.lid3lid1 = 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 :-DWed, 13 Jan 2010 13:04:17 GMTshannonjkhttp://www.sqlservercentral.com/Forums/Topic846659-338-1.aspx[quote][b]Paul White (1/13/2010)[/b][hr][quote][b]forum member (1/13/2010)[/b][hr]My table structure are very big with large quantinty of data so couldn't make a post.[/quote]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...?[/quote]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).Wed, 13 Jan 2010 09:21:39 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic846659-338-1.aspx[quote][b]forum member (1/13/2010)[/b][hr]My table structure are very big with large quantinty of data so couldn't make a post.[/quote]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...?Wed, 13 Jan 2010 03:15:32 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic846659-338-1.aspxHi there,The script provided works but in my case the table 2 is generated and contains data which is a result of other joinsso when i have a left outer join as above i am getting null resulted values from table2but when i do a inner join there exists match and returns the rowsPlease suggest on how to resolve this My table structure are very big with large quantinty of data so couldn't make a postThanksWed, 13 Jan 2010 02:57:42 GMTforum memberhttp://www.sqlservercentral.com/Forums/Topic846659-338-1.aspx[quote][b]CirquedeSQLeil (1/12/2010)[/b][hr]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.[/quote]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...;-)Wed, 13 Jan 2010 00:00:44 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic846659-338-1.aspxIf 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.Tue, 12 Jan 2010 23:39:04 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic846659-338-1.aspxSeems to work fine for me:[code]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.NameFROM @Table1 a LEFT OUTER JOIN @Table2 b ON ( a.id1 = b.id1 AND a.lid1 = b.lid2 AND a.lid3 = b.lid3 );[/code]Tue, 12 Jan 2010 23:35:22 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic846659-338-1.aspxHi,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.Namefrom table1 a Left Outer Join table2 bona.id1=b.id1and a.lid1=b.lid2and a.lid3=b.lid3Please suggest on how to resolve this Thank youTue, 12 Jan 2010 19:52:11 GMTforum member