Use many-to-many joins via a query layer

【解决方案】
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.


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;

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.

【创建及修改时间】
Created: 1/22/2016 Last Modified: 2/23/2016
【原文链接】
http://support.esri.com/en/kno ... 46189