Select Unmatched Records

[razchip]

I need to compare two tables, that contain recipes, when one of the records don't match, I need to see that information. I have constucted the following query, but it doesn't return the unmatched records (there is one extra component in the OldR table.

SELECT DISTINCT NewR.Part, NewR.Ingredients, NewR.Component, NewR.BatchReq
FROM NewR
WHERE (((NewR.Part) Not In (SELECT DISTINCT Part From [OldR]))) OR (((NewR.Ingredients) Not In (SELECT DISTINCT Ingredients From [OldR]))) OR (((NewR.Component) Not In (SELECT DISTINCT Component From [OldR]))) OR (((NewR.BatchReq) Not In (SELECT DISTINCT BatchReq From [OldR])));

Any ideas will be appreciated, thanks.

Thanks for the help.
Greg

 

[Golom]

What you have Selects those records in "NewR" where at least one of the four fields doesn't exist anywhere in "OldR". In the following example
[tt]NewR
Part Ingredients Component BatchReq
A 123 77 V
B 456 88 S
C 123 88 V

OldR
Part Ingredients Component BatchReq
A 456 88 V
B 123 77 S
D 999 66 M
[/tt]
Your query would return the record
[tt]Query
Part Ingredients Component BatchReq
C 123 88 V
[/tt]
Even though three of the 4 field values do exist on "OldR".

Is that what you want?

If you are looking for whole records that don't match then try

SELECT N.Part, N.Ingredients, N.Component, N.BatchReq

FROM NewR N LEFT JOIN OldR O
     ON     N.Part = O.Part
        AND N.Ingredients = O.Ingredients
        AND N.Component = O.Components
        AND N.BatchReq = O.BatchReq

WHERE O.Part IS NULL

Which would return every record in "NewR" in the above example.

 

[razchip]

What I'm looking for is to return any mismatched records, anyone of the four fields could be unmatched.

NewR
Part Ingredients Component BatchReq
A 123 77 V
B 456 88 S
C 789 99 V

OldR
Part Ingredients Component BatchReq
A 123 77 V
B 456 88 S
C 789 93 V

Your query would return the record
Query
Part Ingredients Component BatchReq
C 789 93 V

Thanks for the help.
Greg