Using Subqueries

Question

Hello, I've got a File Geodatabase and I'm trying to use a subquery to compare the data in one table with the data in another table.

Here's my query (using select by attributes) - I'm trying to select the rows in the table Geocoded_20201123 using a subquery that select rows from the table Geocoded_20210412

My_ID NOT IN (SELECT My_ID FROM Geocoded_20210412)

However, this SQL does not validate and when I'm typing, the Geocoded_20210412 table doesn't "pop up" as an option.

Documentation says it should work??

Thanks,

Mike...

bixb0012 · Answer

Is the Geocoded_20210412 table in the same GDB as the data set being selected against?

bixb0012 · Answer

Johannes,

The WHERE clause does allow you to query other feature classes or tables in the same GDB as the data set being selected against.  That said, not ever data source supports is (like shape files).  Also, not every data source supports all the functionality the same.  The Subqueries section of SQL reference for query expressions used in ArcGIS—ArcGIS Pro | Documentation covers most of the details.

JohannesLindner · Answer

AFAIK, anything that requires a "where_clause" (e.g. SelectLayerByAttribute or SearchCursor) can only apply to the target table. You can not use other tables in the query.

So you have to do the sub query yourself: Open the Python window, copy this code and excute it.

exclude_field = "My_ID"
exclude_table = "Geocoded_20210412"
select_table = "Geocoded_20201123"

# SELECT exclude_field FROM exclude_table
exclude_values = [row[0] for row in arcpy.da.SearchCursor(exclude_table, [exclude_field])]

# WHERE exclude_field NOT IN (1, 2, 3)
# WHERE exclude_field NOT IN ('a', 'b', 'c')
try:
    if isinstance(exclude_values[0], str):
        where = "{} NOT IN ('{}')".format(exclude_field, "', '".join(exclude_values))
    else:
        where = "{} NOT IN ({})".format(exclude_field, ", ".join([str(x) for x in exclude_values]))
except IndexError:
    where = None

arcpy.management.SelectLayerByAttribute(select_table, 'NEW_SELECTION', where)