3 table joining - 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

General SQL Server Forums

New to SQL Server Programming

3 table joining

Author

Topic

forumguy
Starting Member

2 Posts

Posted - 2007-11-13 : 16:57:20

Hi guys,

This is regarding the joins...for instance I have three Tables A, B, C. And they are related to each other. If I am joining A->B,B->C then is it necessary to join A->C too? what happens if I omit join A->C? and what happens if I include join A->C too?

Thanks,

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-13 : 17:00:07

The join from A to C is not necessary.

You should test the different queries so that you can see for yourself what results it returns.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-13 : 17:03:01

It might be. It depends on your table design and/or business rules.
But as Tara wrote, it is most likely not necesssary.

E 12°55'05.25"
N 56°04'39.16"

forumguy
Starting Member

2 Posts

Posted - 2007-11-13 : 17:38:14

I asked the question after testing and looking at the result. When i omit A->C I get many records and when I include A->C too then I get very less number of records. If it is not necessary to use A->C then why I am I getting different results?

thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-13 : 17:40:56

It depends on your data. Please post sample data for us.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

arorarahul.0688
Posting Yak Master

125 Posts

Posted - 2007-11-14 : 02:35:02

create table a (i int )
create table b(i int)
create table c(i int)
insert into a values(2)
insert into a values(3)
insert into b values(4)
insert into b values(2)
insert into c values(6)
insert into c values(7)
insert into c values(4)

select * from a inner join b on a.i=b.a
inner join c on b.a=c.i

output
i a i

select * from b inner join c on b.a=c.i
inner join a on b.a=a.i

output
a i i

select * from a full outer join b on a.i=b.a
inner join c on b.a=c.i

output
i a i
null 4 4

this proves that it purely deends upon your buissness requirement that which type of joins you are using and upon your data also

Rahul Arora
MCA 07 Batch
NCCE Israna, Panipat
HRY, INDIA

######################
IMPOSSIBLE = I+M+POSSIBLE

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-14 : 07:39:55

quote:
Originally posted by arorarahul.0688

this proves that it purely deends upon your buissness requirement that which type of joins you are using and upon your data also

Rahul Arora

Proves more than that, proves you apparently don't read prior posts.

Did you really need to write sample script to "prove" that a query and which joins to use depends on the business requirements and data involved?

Poor planning on your part does not constitute an emergency on my part.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-14 : 11:46:32

Rahul, since when are we talking about full outer joins?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources