Is This Content Helpful?
We're glad to know this article was helpful.
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.
Any variation of the Add Query Layer tool can be used to create the layer.
• File > Add Data > Add Query Layer
• Make Query Layer in the Data Management > Layers and Table Views toolbox
The results are the same whatever tool is used.
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;
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).