Outer join with multiple conditions

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Outer join with multiple conditions

Author

Topic

kensai
Posting Yak Master

172 Posts

Posted - 2011-10-28 : 06:09:58

I have two tables I'm joining with a left join:


table1:
id1	name1
1	n1
2	n2

table2:
id2	fkid1	name2
1	1	nn1
2	2	nn2

fkid1 is fk to table1.id1

The left join:


select * 
from join1 t1
left join join2 t2
on t1.id1 = t2.fkid1

gives this result:


id1	name1	id2	fkid1	name2
1	n1	1	1	nn1
2	n2	2	2	nn2

and when I try to filter it like this:


select * 
from join1 t1
left join join2 t2
on t1.id1 = t2.fkid1 and t2.id2 = 1

gives this result:


id1	name1	id2	fkid1	name2
1	n1	1	1	nn1
2	n2	NULL	NULL	NULL

but I want this as result:


id1	name1	id2	fkid1	name2
1	n1	1	1	nn1

Is it possible to do this, using on condition and not where condition?

I'm actually trying to join multiple tables with a more complex left join and my conclusion was that I need to use on condition and not where thus my problem..

I'd appreciate any help with this.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-10-28 : 06:16:46

I'm sure you don't want the OUTER JOIN to work different

What you should do is: WHERE id2 IS NOT NULL

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-28 : 06:22:47

if thats what you want. wont below suffice?


select * 
from join1 t1
inner join join2 t2
on t1.id1 = t2.fkid1 
and t2.id2 = 1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources