Friday, March 23, 2012

Outer join blues-sql 2005

Hi there!!

I want to have right outer join on two or more column between two tables.how can we do that in SQL 2005 as it does not allow us to use =* operator.

basically i need below query in sql 2005

Select * from

T1,T2

where

T1.c1=* T2.c1 and

T1.c2=* T2.c2

I tried this one

Select * from

T1

right outer join

T2

on

T1.c1=T2.c1 and

T1.c2=T2.c2

--But this gave me different result.

Thanks

Rahul Kumar, Software Engineer

Rahul:

I mocked this up with these two tables with the data that follows:

create table dbo.T1
( c1 integer,
c2 integer
)
go

create table dbo.T2
( c1 integer,
c2 integer
)
go

insert into t1 values (1, 1)
insert into t1 values (2, 1)
insert into t1 values (4, 4)
select * from t1
go

insert into t2 values (1,1)
insert into t2 values (2,2)
insert into t2 values (3,3)
select * from t2
go

When I tested with SQL Server 2000 results, I got the same results with the RIGHT JOIN syntax as I did with the =* syntax:

Select * from
T1,T2
where
T1.c1=* T2.c1 and
T1.c2=* T2.c2

-- c1 c2 c1 c2
-- -- -- -- --
-- 1 1 1 1
-- NULL NULL 2 2
-- NULL NULL 3 3


Select * from
T1
right outer join
T2
on
T1.c1=T2.c1 and
T1.c2=T2.c2


-- c1 c2 c1 c2
-- -- -- -- --
-- 1 1 1 1
-- NULL NULL 2 2
-- NULL NULL 3 3

When I run the RIGHT OUTER JOIN syntax on my SQL 2005 server I get the same results; what results are you getting?


Dave

|||

the syntax u have used is correct...

Select * from

T1

right outer join

T2

on

T1.c1=T2.c1 and

T1.c2=T2.c2

just see if u need right outer or left or full outer join......or maybe inner....=* is same as right outer....just that 'right outer join' , is an ansi syntax..

No comments:

Post a Comment