About join

[shenliang1985]

I have Created two tables to test join key in sql server,but the result is
not what I thougt.Why?Wish all your explains.Thanks.I give all the sql code
in comment.
--The table name is first for tesing fulljoin
/*
create table testfulljoin
(a char(4),
b char(4),
c char(4))
insert testfulljoin(a,b,c)
select 'a','b','c' union all
select 'b','a','d' union all
select 'c','d','e' union all
select 'd','f','g'
*/

/*
create table testfulljoin2
(b char(4),
c char(4),
d char(4))
insert testfulljoin2(b,c,d)
select 'b','c','d' union all
select 'd','e','g' union all
select 'f','d','g' union all
select 'd','e','c'
*/

When testfulljoin2 left join testfulljoin the result is:
a b c d
c d e g
NULL NULL NULL g
c d e c

Why result is not like what I' v aways told:
a b c d
c d e g
null f d g
c d e c

If not why testfulljoin2 right join testfulljoin the result is
a b c d
b a d NULL
c d e g
c d e c
d f g NULL

[shenliang1985]

I found the answer myself
Use :


select a.a ,b.* from testfulljoin2 as b left join testfulljoin as a on a.b=b.b and a.c=b.c;

[amitpatel66]

I found the answer myself
Use :


select a.a ,b.* from testfulljoin2 as b left join testfulljoin as a on a.b=b.b and a.c=b.c;

Thas good that you found the solution by yourself. Self learning is the best way to learn new things.

MODERATOR