Select to view content in your preferred language

Definition Query Based on Related Table

2144
4
12-06-2010 10:15 AM
Status: Open
Labels (1)
BrandonJones
New Contributor II

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.

4 Comments
ChristiNelson1
has this been enabled yet? 
ChrisFox
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})
BenjaminKnott2
The Definition Query fields from a joined table are available.  Why not a related table?
RichardFairhurst

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