find the unmatched records between two tables

Question

so i am looking to replicate the "find unmatched query wizard" from microsoft access in python.

basically i have 2 tables and i want to output all the records from the one table that do not match the records of the other table based on a common ID.

in theory it seems simple enough but i can't figure out how to do it.

any help would be greatly appreciated!

thanks,

max

JeffWard · Answer

I have a similar thing that I do in model builder that you could export as a script.  When I do parcel updates, I get a new ownership table that I compare to the old one to find new records and changes in fields like acres parcel numbers and physical addresses.

1-Join the new table to the old table based on the common id
2-Make a table view with an expression selecting new records and records that have fields that don't match, also limit the view to show only relevant fields.
3-Create a new table and populate it with the rows of the table query.

I first do a join based on the new table to the old table using the common ID field to join them together.  I do that with the Add Join tool in the Joins subtoolbox in the Data Management toolbox.  I then run the resulting table through the Make Table View tool in the Layers and Table Views subtoolbox of the Data Management toolbox.  When I run that tool I specify a query that will select the new records (OldTableCommonID IS NULL) and any changed fields (NewTableField <> OldTableField) all of these connected by the OR logical operator.  I also limit the table view to the four fields that I am comparing.  I then create a new table with the Create Table tool in the Table subtoolbox of the Data Management toolbox.  I base the fields of the new table on the table view created in the previous tool by having the table view be the input for the Template Table Name option in the Make Table tool.  I then use the Append tool in the General subtoolbox of the Data Management toolbox to populate the new table using the records from the table view.  I then go a step further by adding a field to the new table that would show which field has the change.  I populate it by doing the Calculate Field tool in the Fields subtoolbox of the Data Management toolbox.

That description is probably more than you needed, but I hope it helps

curtvprice · Answer

Here's an approach that may work for you.

After you have a selected set of rows, you can run CopyRows, CopyFeatures, etc.# select no-matches
arcpy.AddJoin(tableview1,field1,table2,field2) # "KEEP_ALL" is the default
prefix = arcpy.Describe(table2).Name
strExpr = "%s.%s IS NULL" % (prefix,field2)
arcpy.SelectLayerByAttribute(table1,strExpr)
arcpy.RemoveJoin(tableview1,prefix)

# select matches
arcpy.AddJoin(tableview1,field1,table2,field2,"KEEP_COMMON")
prefix = arcpy.Describe(table2).Name
arcpy.SelectLayerByAttribute(table1,"0 <> 1") # select all
arcpy.RemoveJoin(tableview1,prefix)

HemingZhu · Answer

so i am looking to replicate the "find unmatched query wizard" from microsoft access in python.

basically i have 2 tables and i want to output all the records from the one table that do not match the records of the other table based on a common ID.

in theory it seems simple enough but i can't figure out how to do it.

any help would be greatly appreciated!

thanks,

max

You could use arcpy.TableSelect_analysis or arcpy.TableToTable_conversion. First, find the other table ids (using search cursor) and compiled them into string like ids ="1,2,3,...22". Secondly define the expression parameter (where_clause) like where_clause ="YouIDfield not in (" + ids + ")".  Then using either one of the methods i mentioned above with the where_clause as the expression parameter....