February 12, 2015 at 12:54 pm
Hi
I have two table People and Employee, both have firstname and lastname as fields
I want to display only the names that don't match on firstname and lastname
Thanks
Joe
February 12, 2015 at 1:05 pm
jbalbo (2/12/2015)
HiI have two table People and Employee, both have firstname and lastname as fields
I want to display only the names that don't match on firstname and lastname
Thanks
Joe
Hello Joe, what have you tried so far?
So what you want is to find where a firstname / lastname combination is in one table but not the other?
Look at full join.
February 12, 2015 at 1:43 pm
Sorry I left out that the tables do have an ID field to match on
I did come up with this but wondering if there was a better way?
Thanks
SELECT people.id, people.[First Name] AS EFirst, people.[Last Name] AS ELast,
people_Identifiers.[Number/Code] AS ENumber, Employee.FirstName AS UFirst, Employee.LastName AS ULast,
Employee.EmployeeNumber AS UNumber
FROM people INNER JOIN
people_Identifiers ON people.ObjectID = people_Identifiers.Person INNER JOIN
Employee ON people_Identifiers.[Number/Code] = Employee.EmployeeNumber AND
people.[First Name] <> Employee.FirstName AND people.[Last Name] <> Employee.LastName
February 12, 2015 at 2:16 pm
I would change the join conditions.
SELECT people.id,
people.[First Name] AS EFirst,
people.[Last Name] AS ELast,
people_Identifiers.[Number/Code] AS ENumber,
Employee.FirstName AS UFirst,
Employee.LastName AS ULast,
Employee.EmployeeNumber AS UNumber
FROM people
INNER JOIN people_Identifiers ON people.ObjectID = people_Identifiers.Person
INNER JOIN Employee ON people_Identifiers.[Number/Code] = Employee.EmployeeNumber
AND (people.[First Name] <> Employee.FirstName
OR people.[Last Name] <> Employee.LastName)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply