left join - 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

left join

Author

Topic

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-03-06 : 22:17:39

Hi all. My query works fine, it generates reports but not my expected result.

select d.fullname, p.nickname, p.birthdate, p.birthplace,
p.gender, p.civilstatus, p.religion, p.nationality, p. weight, p.height,
p.haircolor, p.eyecolor, p.complexion, p.bodybuilt, p.picture, p.dialectspoken,
d.mobilephone, d.prprovince,[Age] = dbo.F_AGE_IN_YEARS( birthdate, getdate() ),
c.name, c.address, c.telno, c.email, c.occupation, ed.year1, ed.year2, ed.degree, sch.schname
from hremployees as e
inner join psdatacenter as d on e.empdcno = d.dcno
inner join pspersonaldata as p on e.empdcno = p.dcno
left join hrappempcharrefs as c on e.empdcno = c.empdcno
left join hrappempeducs as ed on e.empdcno = ed.empdcno
left join hrsetschools as sch on ed.schoolcode = sch.schcode

the above query gives a 77 records

if i ran "select * from hremployees" generates 60 records

i think the error is in the left joining.
hrappempcharrefs, hrappempeducs and hrsetschools must be left joined to hremployees.

thanks
-Ron-

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-06 : 22:22:11

one of the table must be one to many relationship to the hremployees table

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-03-06 : 23:01:28

yes. all the tables except hrsetschools are one to many relationship to the hremployees.

In this example http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80017 , yea it works fine.
But in my actual tables, I can't get the correct result. Or something i missed out?

If I ran,
select d.fullname, p.nickname, p.birthdate, p.birthplace,
p.gender, p.civilstatus, p.religion, p.nationality, p. weight, p.height,
p.haircolor, p.eyecolor, p.complexion, p.bodybuilt, p.picture, p.dialectspoken,
d.mobilephone, d.prprovince,[Age] = dbo.F_AGE_IN_YEARS( birthdate, getdate() )
from hremployees as e
inner join psdatacenter as d on e.empdcno = d.dcno
inner join pspersonaldata as p on e.empdcno = p.dcno

It works fine. I can get 60 records.

If I add hrappempcharrefs, the record will become 61.
If I add hrappempeducs and/or hrsetschools , the record will become 77.

Thanks
-Ron-

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-03-07 : 00:05:52

I know what is wrong with my tables. I have duplicate empdcno in some tables.
Thanks
-Ron-

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2007-03-07 : 06:52:42

"I have duplicate empdcno in some tables"....sounds like you need additional constraints/indicies to prevent same!!

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-03-07 : 07:35:51

I have solved it. thanks.

-CLOSED-
-Ron-