topic Re: Using Subqueries in ArcGIS Pro Questions

Using Subqueries

MikeBHamsa — Mon, 10 Jan 2022 22:13:59 GMT

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

Re: Using Subqueries

JohannesLindner — Tue, 11 Jan 2022 11:10:43 GMT

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)

Re: Using Subqueries

JoshuaBixby — Wed, 12 Jan 2022 15:45:34 GMT

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.

Re: Using Subqueries

JoshuaBixby — Wed, 12 Jan 2022 15:46:32 GMT

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