joining multiple 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

joining multiple tables

Author

Topic

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-03-05 : 06:58:15

table - employee
empdcno name
1 jon
2 peter
3 john
4 n1
5 n2
6 n3
7 n4
8 n5
9 n6
10 n7

table - personalinfo
empdcno telno address
1 111 aaa
2 222 bbb
3 333 ccc
4 444 ddd
5 555 eee
6 666 fff
7 777 ggg
8 888 hhh
9 999 iii
10 000 jjj

table - hrappempeducs
empdcno schoolcode degree
1 A1 BSCS
3 A2 BSIT
5 A3 degree1
9 A3 degree2
10 A2 degree3

table - hrsetschools
schoolcode schname schaddress
A1 Harvard usa
A2 LaSalle philippines
A3 UP india
A4 s1 peru
A5 s2 japan

Result expected:

name telno address degree schname

jon 111 aaa BSCS Harvard
peter 222 bbb null null
john 333 ccc BSIT LaSalle
n1 444 ddd null null
n2 555 eee degree1 UP
n3 666 fff null null
n4 777 ggg null null
n5 888 hhh null null
n6 999 iii degree2 UP
n7 000 jjj degree3 LaSalle

I am not quite familiar with joins.
Thanks
-Ron-

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-05 : 07:21:36

[code]
declare @employee table
(
empdcno int,
name varchar(10)
)
insert into @employee
select 1, 'jon' union all
select 2, 'peter' union all
select 3, 'john' union all
select 4, 'n1' union all
select 5, 'n2' union all
select 6, 'n3' union all
select 7, 'n4' union all
select 8, 'n5' union all
select 9, 'n6' union all
select 10, 'n7'

declare @personalinfo table
(
empdcno int,
telno int,
address varchar(10)
)
insert into @personalinfo
select 1, 111, 'aaa' union all
select 2, 222, 'bbb' union all
select 3, 333, 'ccc' union all
select 4, 444, 'ddd' union all
select 5, 555, 'eee' union all
select 6, 666, 'fff' union all
select 7, 777, 'ggg' union all
select 8, 888, 'hhh' union all
select 9, 999, 'iii' union all
select 10, 000, 'jjj'

declare @hrappempeducs table
(
empdcno int,
schoolcode varchar(10),
degree varchar(10)
)
insert into @hrappempeducs
select 1, 'A1', 'BSCS' union all
select 3, 'A2', 'BSIT' union all
select 5, 'A3', 'degree1' union all
select 9, 'A3', 'degree2' union all
select 10, 'A2', 'degree3'

declare @hrsetschools table
(
schoolcode varchar(10),
schname varchar(10),
schaddress varchar(15)
)
insert into @hrsetschools
select 'A1', 'Harvard', 'usa' union all
select 'A2', 'LaSalle', 'philippines' union all
select 'A3', 'UP', 'india' union all
select 'A4', 's1', 'peru' union all
select 'A5', 's2', 'japan'

select e.name, p.telno, p.address, h.degree, s.schname
from @employee e
~~inner~~ left join @personalinfo p on e.empdcno = p.empdcno
~~inner~~ left join @hrappempeducs h on e.empdcno = h.empdcno
~~inner~~ left join @hrsetschools s on h.schoolcode = s.schoolcode

/*
name telno address degree schname
---------- ----------- ---------- ---------- ----------
jon 111 aaa BSCS Harvard
peter 222 bbb NULL NULL
john 333 ccc BSIT LaSalle
n1 444 ddd NULL NULL
n2 555 eee degree1 UP
n3 666 fff NULL NULL
n4 777 ggg NULL NULL
n5 888 hhh NULL NULL
n6 999 iii degree2 UP
n7 0 jjj degree3 LaSalle
*/
[/code]

[EDIT]
Change to LEFT JOIN as per Harsh suggestion.

KH
[/EDIT]

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-03-05 : 08:41:32

Thanks KH for the time. But, the result I want to come up is:

name telno address degree schname
/*
jon 111 aaa BSCS Harvard
peter 222 bbb null null
john 333 ccc BSIT LaSalle
n1 444 ddd null null
n2 555 eee degree1 UP
n3 666 fff null null
n4 777 ggg null null
n5 888 hhh null null
n6 999 iii degree2 UP
n7 000 jjj degree3 LaSalle
*/

The result is the record of all employees.

-Ron-

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-05 : 08:47:48

Just change all Inner joins to LEFT joins.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-03-05 : 20:24:29

Thanks KH and Harsh.
-Ron-

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources