Use many-to-many joins via a query layer

Summary

Many-to-many joins are difficult to handle with ArcGIS. The table grid in ArcMap requires a unique identifier for each row in a table and this condition is violated by joins of this type. Currently the table grid displays one of the matching rows. The matching row selected for display is effectively random.

Through the use of a query layer, many-to-many joins can be more effectively handled. The table grid behaves similarly to the RDBMS and queries against the data are more consistent with the expected behavior.

Procedure

1. In ArcMap bring up the New Query Layer tool: File > Add Data > Add Query Layer.
2. Under Connections, connect to the data source.
3. Enter a query establishing a left outer join between the tables. Do not use asterisks (*) in the SELECT list.
   
   Provide a qualified list of all of the fields to be returned. Field names cannot be duplicates and must be aliased.
   
   Table1: streets
   Table2: busroutes
   Join Field: street_id
   
   

   SELECT streets.objectid, busroutes.objectid as busoid, streets.street_id,
   busroutes.route_id, busroutes.route_name, streets.street_name, streets.street_block_length
   FROM streets LEFT OUTER JOIN busroutes ON streets.street_id = busroutes.street_id;

   
   
   

   Note:
   A WHERE clause can be used if needed. For example, it may be desirable to return only the rows for a particular bus route,  (WHERE busroutes.route_id = 12).

   
   
4. Use the ObjectID from each table as unique identifier fields.
5. Click Finish.
   
   The query layer is added to the Table of Contents. All matching rows display in the table grid. Duplicate geometries display when there are multiple matches.
   
   This works for ArcMap and ArcGIS Pro. It is persisted by saving the map document or project. If published to a map service, the query layer can be queried and displayed.