idea Definition Query Based on Related Table in ArcGIS Pro Ideas

Definition Query Based on Related Table

BrandonJones — Thu, 28 Jul 2022 19:40:26 GMT

It would be nice if I could perform a definition query based on fields in a related table in a 1:M or M:M relationship.

Re: Definition Query Based on Related Table

ChristiNelson1 — Fri, 10 Aug 2012 14:56:43 GMT

has this been enabled yet?

Re: Definition Query Based on Related Table

ChrisFox — Fri, 10 Aug 2012 17:45:00 GMT

You can do this now without a relationship class, but it requires both features classes be in the same geodatabase. Below is an example query:

"STATE_NAME" IN (SELECT "NAME" FROM states WHERE "POP1990" > 1000000)

In the query above I am selecting the Cities by the State they are related to whose State's population is greater than 10 million. Below is the format of the query explained

"{Primary Key Name}" IN (SELECT "{Foriegn Key Name}" FROM {Destination Table/FC Name} WHERE {WHERE clause to be run for related table})

Re: Definition Query Based on Related Table

BenjaminKnott2 — Tue, 09 Oct 2012 22:24:34 GMT

The Definition Query fields from a joined table are available. Why not a related table?

Re: Definition Query Based on Related Table

RichardFairhurst — Thu, 15 Sep 2022 21:41:56 GMT

@ChrisFoxI had never tried the SQL syntax you outlined, but this expression does work in ArcMap and ArcGIS Pro for related data in the same geodatabase. This syntax is only briefly discussed in the Subqueries section of the SQL reference for query expressions used in ArcGIS website. That section doesn't include an example that was quite as clear or useful to me as the one you have given. I frankly would like to see your example added to that section, since if it had been there years ago I would have been much more likely to see the usefulness of this approach and not had to develop some of the workarounds I came up with.

Your example works for file geodatabases as well, despite the warning that file geodatabases only offer limited support for subqueries. The Subqueries section basically says that help for using them is specific to the DBMS documentation of my enterprise geodatabase. Since I've mostly worked with file geodatabases that limitation warning had kept me from even trying to figure out or use Subqueries, but based on the example you have outlined I believe I actually now have a use for them. Thanks.