Options for joining to queries

Question

In ArcGIS Pro, using an enterprise geodatabase:

What are the options for dynamically joining from a FC to a related query?

* Use the Add Join GP tool to join to a database view.* Fields from the joined view are shown in attribute table.

* Use the Add Join GP tool to join to a query layer.* Fields from the joined query layer are shown in attribute table.

* Use a definition query and a subquery to filter rows.* Example: EVENT_ID IN (SELECT EVENT_ID FROM …). See #1 in a similar post here.
* No fields from the “joined” subquery are shown in the attribute table.
* The nice thing about this option is that it avoids the Add Join GP tool. In my experience, joins are sometimes buggy in ArcGIS Pro. This approach avoids those issues.

* Use the Add Join GP tool to join to a table that has a definition query.* Example: One-to-first joins: Control what related record is used
* Fields from the joined table are shown in attribute table.

For 1-4 above, the fields from the source FC are editable. The fields in the join table aren’t editable.

* Join using SQL in a database view or a query layer.* Fields from the joined table can be included in the result.
* None of the fields are editable.

Any comments or other options?

Oracle 18c 10.7.1 EGDB.

Bud · Answer

Thanks @JMalik.

JMalik · Answer

You have listed several options for dynamically joining from a feature class to a related query in an enterprise geodatabase using ArcGIS Pro:

* Use the Add Join GP tool to join to a database view.
* Use the Add Join GP tool to join to a query layer.
* Use a definition query and a subquery to filter rows.
* Use the Add Join GP tool to join to a table that has a definition query.

You also mentioned that for options 1-4 above, the fields from the source feature class are editable while the fields in the join table are not editable.

* Join using SQL in a database view or a query layer. Fields from the joined table can be included in the result but none of the fields are editable.

Some other options you could consider include:

* Creating a relationship class in the geodatabase. This allows you to define the relationship between two tables and can be used to create a dynamic join between the feature class and the related table. The fields from the related table will be shown in the attribute table and can be edited as per the relationship class settings.

* Using the 'Join Field' tool. This tool can be used to join two tables based on a common field and the fields from the joined table will be shown in the attribute table and can be edited as per the join settings.

* Using the 'Relate' tool. This tool can be used to establish a relationship between two layers in a map and the fields from the related table will be shown in the attribute table and can be edited as per the relate settings.

All these options are dependent on your specific use case, the database management system you are using and your organization's policies and standards.

In your case, you are using Oracle 18c 10.7.1 Enterprise Geodatabase, so you may want to check the documentation and best practices provided by Oracle and Esri on how to work with joins in an enterprise geodatabase.