SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Left Outer Join


Left Outer Join

Author
Message
forum member
forum member
SSC Journeyman
SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

Group: General Forum Members
Points: 98 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
Paul White
Paul White
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16432 Visits: 11355
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
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33632 Visits: 18560
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

Paul White
Paul White
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16432 Visits: 11355
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
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
forum member
forum member
SSC Journeyman
SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

Group: General Forum Members
Points: 98 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
Paul White
Paul White
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16432 Visits: 11355
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
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33632 Visits: 18560
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

shannonjk
shannonjk
Right there with Babe
Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)Right there with Babe (714 reputation)

Group: General Forum Members
Points: 714 Visits: 840
Paul, I giggled when I saw that Smile

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

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 :-D

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


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search