I need a little SQL help

[auguststartz]

With Today being Friday almost all of our IT dept is on vacation, so I get stuck helping with some database problems we are having.

Basically I need to query 3 tables and find any employee names that do not match there emp number.
The tables are named Employee, PJEMPLOY and XHR_Employee. The field with the employee number and always EmpId and the name field is always Name
I know how to pull complete lists for each table and I was thinking of exporting to Excel and trying to filter the results there but before I try that I thought I would see if anyone at anandtech had a idea.

Can anyone help me with this script?


Thanks

 

[Beau]

without a full table structure and database type, I can't do much.

 

[auguststartz]

Its a MSSQL database.

As for the full table structure there are 500+ Tables each with 10-30ish fields.
Do you need the full tables and fields or just for the 3 tables I need to search?

Thanks for the help.

 

[Crusty]

Just the 3 tables and fields needed.

 

[MrChad]

W3 Schools offers a pretty good free SQL tutorial.

 

[auguststartz]

Thanks,

Here is the structure for the Employees table:
Addr1
Addr2
Attn
BirthDate
CalYr
ChkNbr
City
Country
CpnyID
Crtd_DateTime
Crtd_Prog
Crtd_User
CurrBatNbr
CurrCheckCalc
CurrCheckPrint
CurrCheckType
CurrEarn
CurrNet
CurrPayPerEndDate
CurrPayPerNbr
CurrPayPerStrtDate
CurrStdUnitRate
Department
DfltEarnType
DfltExpAcct
DfltExpSub
DfltOthrExmpt
DfltPersExmpt
DfltWrkloc
DirectDeposit
EmpId
EndDate
Fax
HomeUnion
LastPaidDate
LUpd_DateTime
LUpd_Prog
LUpd_User
MagW2
MarStat
MaxGarnWarn
MedGovtEmpl
Name
NoteId
PayGrpId
PayType
PercentDispEarn
Phone
Salut
Shift
SSN
State
Status
Statutory
StdSlry
StdUnitRate
StrtDate
User1
User2
User3
WCCode
YtdEarn
Zip
tstamp

Here is the structure for PJEMPLOYEE
BaseCuryId
CpnyId
crtd_datetime
crtd_prog
crtd_user
CuryId
CuryRateType
date_hired
date_terminated
EmpID
emp_status
emp_type_cd
em_id01
em_id02
em_id03
em_id04
em_id05
em_id06
em_id07
em_id08
gl_subacct
lupd_datetime
lupd_prog
lupd_user
manager1
manager2
MSPData
MSPInterface
MSPRes_UID
MSPType
name
noteid
placeholder
stdday
Stdweek
Subcontractor
user_id
tstamp

And finally XHR_EMployee
Address
AddressLine2
Citizenship
CIty
Country
CpnyId
Crtd_DateTime
Crtd_Prog
Crtd_User
DateHired
DateOfBirth
DateTerminated
Department
eMailAddress
EmergContact
EmergHomePhone
EmergRelation
EmergWorkPhone
EmpId
Fax
Gender
Handicap
INSPermitExp
INSPermitNbr
INSPermitType
LUpd_DateTime
LUpd_Prog
LUpd_User
MaritalStatus
Name
NoteId
PassportExp
PassportNbr
PayrollSynchronized
PersonId
Phone
PrimaryWrkLoc
RaceAfroAmer
RaceAmerIndian
RaceCaucasian
RaceDescr
RaceHispanic
RaceOriental
RaceOther00
RaceOther01
ServiceTimeAdj
SocSecNbr
State
Status
SupervisorId
User1
Vehicle1Descr
Vehicle1Plate
Vehicle1State
Vehicle2Descr
Vehicle2Plate
Vehicle2State
VestedTimeAdj
Veteran
Zip
tstamp

All I really need to know is all the records that have the same EmpID in all 3 tables but diffrent info in the Name field.

Does that make any since?
Thanks

 

[auguststartz]

Originally posted by: MrChad
W3 Schools offers a pretty good free SQL tutorial.

Thanks I will take a look

 

[shutterFly]

Try this:

SELECT EmpId,
e1.Name,
e2.Name,
e3.Name
FROM Employees e1
INNER JOIN PJEMPLOYEE e2
ON e1.EmpID = e2.EmpID
INNER JOIN XHR_EMployee e3
ON e2.EmpID = e3.EmpID
WHERE e1.Name != e2.Name
OR e2.Name != e3.Name
OR e1.Name != e3.Name