Join - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Join

Author

Topic

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2009-02-10 : 05:11:10

is it the correct join to get all the infomation from White table based on ip from Rd table.

procedure [dbo].[aaa]
@ip varchar(20)
as
select w.nric as nric,w.rid as rid,r.ip as ip
from White w left join Rd r
on w.rid=r.id
where r.ip=@ip

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-10 : 05:12:24

[code]create procedure dbo.aaa
(
@ip varchar(20)
)
as

set nocount on

select w.nric as nric,
w.rid as rid,
r.ip as ip
from White as w
left join Rd as r on w.rid = r.id
AND r.ip = @ip[/code]

E 12°55'05.63"
N 56°04'39.26"

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-02-10 : 05:12:36

It is correct join

Jai Krishna

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-10 : 05:17:27

quote:
Originally posted by Jai Krishna

It is correct join

The WHERE clause is working against the JOIN type and thus forcing the JOIN (even though it is written as a LEFT JOIN) to an INNER JOIN.

We can't tell if that is the correct behavious because we don't know the business rules.
But for some reason OP have choose to use a LEFT JOIN and by that in mind I changed the WHERE clause to work with LEFT JOIN, by changing the WHERE to an additional JOIN critiera.

E 12°55'05.63"
N 56°04'39.26"

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-02-10 : 05:35:54

Did not notice the WHERE Clause

Jai Krishna

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-10 : 05:44:53

see this too
http://weblogs.sqlteam.com/jeffs/archive/2007/05/14/60205.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources