three tables - show different records that exist in one table

  • Thread starter Thread starter DirtyRat
  • Start date Start date
D

DirtyRat

Hello Everyone

I have three tables.
This is the pseudo code of what I want to do with the tables.
If FirstName and LastName exists in payroll_current AND exists in
payroll_terminated then do NOT show record in nds_all

Constraints are that the data from our Novell NDS tree does not have a
unique identifier like the emp no field that exists in the payroll tables. I
am trying to use the FirstName and LastName fields to create the joins but
have not been successful.

Any help appreciated (I hope this formats ok for everyone.)

Any help greatly appreciated

Regards

Jonathon Egan

nds_all ID Context UserID FirstName Surname LastLoginTime GWUserID
16 xxx.mhs xxxx xxxxx xxxx 30/06/2000 1:35:45 AM

17 xxx.mhs xxx xxx xxxxx 8/02/2002 5:04:10 AM

18 xxx.mhs xxxx xxxx xxxx 15/10/2003 10:37:25 PM SABEY
19 xxx.mhs xxxx
accounts


20 xxxx.mhs xxxx xxxx xxxx 30/04/2002 10:33:45 PM



payroll-current Emp No Location Name Hired Surname FirstName SecondName
ThirdName
xxxx xxx xxxx 31/08/1987 xxxx xxxx xxxx

xxxx xxx xxxx 31/10/1989 xxxx xxxx xxxx



payroll_terminated Emp No Location Name Hired Terminated Surname FirstName
ThirdName Field10
A229 xxxxx xxxx 25/06/2001 25/06/2002 xxxx xxxx xxxx xxxx
A240 xxxx xxxx 18/02/2002 28/07/2002 xxxx xxxx xxxx

A245 xxxx xxx 26/05/2002 25/08/2002 xxxx xxxx
 
Possibly something like the following would work.

SELECT N.UserID, N.FirstName, N.Surname
FROM (Payroll_Current as C
INNER JOIN Payroll_Terminated as T
ON C.FirstName = T.FirstName AND
C.SurName = T.Surname)
RIGHT JOIN NDS_All as N
ON N.FirstName = C.FirstName
AND N.Surname = C.Surname
WHERE C.Surname is Null

If that doesn't work then build a query joining the Payroll and terminated
tables on the FirstName and Surnames. Save it and then use the Unmatched query
wizard to find records in NDS_All that don't exist in the new query.
 
John

Awesome stuff I will try your code example and post info on how it goes.

I have tried the unmatched query but not the way you suggested. I will try
it right now.


Regards


Jonathon Egan
 
Back
Top