Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Left Outer Join Expand / Collapse
Author
Message
Posted Tuesday, January 12, 2010 7:52 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 1, 2011 10:58 AM
Points: 76, Visits: 906
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
Post #846659
Posted Tuesday, January 12, 2010 11:35 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:38 PM
Points: 11,194, Visits: 11,104
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
);





Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #846702
Posted Tuesday, January 12, 2010 11:39 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:02 PM
Points: 21,330, Visits: 15,004
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #846703
Posted Wednesday, January 13, 2010 12:00 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:38 PM
Points: 11,194, Visits: 11,104
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...




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #846715
Posted Wednesday, January 13, 2010 2:57 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 1, 2011 10:58 AM
Points: 76, Visits: 906
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
Post #846767
Posted Wednesday, January 13, 2010 3:15 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:38 PM
Points: 11,194, Visits: 11,104
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #846773
Posted Wednesday, January 13, 2010 9:21 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:02 PM
Points: 21,330, Visits: 15,004
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #847006
Posted Wednesday, January 13, 2010 1:04 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, June 19, 2014 6:36 PM
Points: 586, Visits: 822
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/
Post #847151
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse