[resolved]-joining 3 tables - 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

[resolved]-joining 3 tables

Author

Topic

gparadis3
Starting Member

11 Posts

Posted - 2005-10-14 : 04:24:19

Hi all.. i have a problem.

Let say i have 3 tables. Let's name it Main, table2, table3. in table Main, there are 4 colums(M_id,M_name M_tbl2, M_tbl3). In table2 and table3, each has 3 colums. For table2 the colums are id, tbl2Name, tbl2Desc and for table3(id, tbl3Name, tbl3Desc).

M_tbl2 will refer to id in table2 and M_tbl3 will refer to id in table3. How am i going to get M_id, M_name, tbl2Name, tbl3Name in one query?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-14 : 04:31:34

Try this

Select M.M_id, M.M_name, T2.tbl2Name, T3.tbl3Name
from Main M
inner join table2 T2 on M.M_tbl2=T2.id
inner join Table3 T3 on M.M_tbl3=T3.id

Madhivanan

Failing to plan is Planning to fail

gparadis3
Starting Member

11 Posts

Posted - 2005-10-14 : 04:49:36

Yeah.. its working but M_tbl2 and M_tbl3 can be null. I still need the null value. your code will produce result when M_tbl2 and M_tbl3 are not null.

Refer below:

Main table
M_id____M_name___M_tbl2___M_tbl3
1_______A________1________<null>
2_______B________2________1

Table2
id___tbl2Name___tbl2Desc
1____bla1_______...
2____bla2_______...

Table3
id___tbl3Name___tbl3Desc
1____blu1_______...
2____blu2_______...

Your code will produce something like this

Result
M_id____M_name___Tbl2name___tbl3name
2_______B________bla2_______blu1

i want the result to look like below
M_id____M_name___Tbl2name___Tbl3name
1_______A________bla1_______<null>
2_______B________bla2_______blu1

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-14 : 05:23:03

Instead of Inner Join use Left Join

Madhivanan

Failing to plan is Planning to fail

gparadis3
Starting Member

11 Posts

Posted - 2005-10-14 : 17:40:08

quote:
Originally posted by madhivanan

Instead of Inner Join use Left Join

Madhivanan

Failing to plan is Planning to fail

Thanx master. it's working..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources