Using Subqueries

348
3
01-10-2022 02:13 PM
MikeBHamsa
New Contributor II

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...

0 Kudos
3 Replies
JohannesLindner
MVP Regular Contributor

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)

 


Have a great day!
Johannes
0 Kudos
JoshuaBixby
MVP Esteemed Contributor

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.

0 Kudos
JoshuaBixby
MVP Esteemed Contributor

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

0 Kudos