Find subtle differences
Two Tables to compare: "Old Data" and "New Data"
I need to query and find any difference at all in the following fields:
Full Name, Grade, Abbreviation, Home, TechID.
Key field is "Full Name"
Is there a single query that will show me any difference in any of those fields?
I would like to show the results from table "New Data", the old doesn't matter.
I need to query and find any difference at all in the following fields:
Full Name, Grade, Abbreviation, Home, TechID.
Key field is "Full Name"
Is there a single query that will show me any difference in any of those fields?
I would like to show the results from table "New Data", the old doesn't matter.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Find unmatched was only giving me one column's difference. However the SQL code above worked fine.
Bad choice, as there can be more than one person with the same name, i.e. John Smith, Jane Doe, Paddy O'Flavo, and in those cases the name is not unique.
>I need to query and find any difference ... Full Name
>Key field is "Full Name"
You can have one or the other, but not both, as SQL needs to know how to join these two tables, and if you're using Full Name then it looks for rows where the two values are the same, and has no way of knowing if different rows are a legitimate difference (Jim Horn vs. James Horn), or a difference to ignor (Danny Wareham vs. Miriam Bizup).
>Is there a single query that will show me any difference in any of those fields?
Something like..
SELECT n.[Full Name], n.Grade, n.Abbreviation, n.Home, n.TechID
FROM [New Data] n
INNER JOIN [Old Data] o ON n.[Full Name] = o.[Full Name]
WHERE n.Grade <> o.Grade OR
n.Abbreviation <> o.Abbreviation OR
n.Home <> o.Home OR
n.TechID <> o.TechID