How to join using Make Query Table?

Bud — Fri, 08 Mar 2024 17:03:08 GMT

ArcGIS Pro 3.2.2; File Geodatabase:

I want to learn how to use Make Query Table, possibly as an alternative join mechanism when Add Join isn't suitable, such as:

Select By Attributes on joined data — For rows that are 1:M, all rows in the join table get selected, despite selection criteria
https://community.esri.com/t5/arcgis-pro-questions/select-by-attributes-on-joined-data-for-rows-that/td-p/1390893

The docs say:

Make Query Table (Data Management)
The query can be used to join several tables or return a subset of fields or rows from the original data in the database.

I thought Make Query Table might do a 1:1 or 1:M join between two tables using a common join field. But when I tested it, the resulting QueryTable isn't what I expected:

Is it doing some sort of cartesian product/CROSS JOIN?

What am I missing? What's the intended use case? How can I do a 1:1 or 1:M join?

Re: How to join using Make Query Table?

Bud — Fri, 15 Mar 2024 04:01:09 GMT

It looks like the join fields need to be specified in the WHERE clause SQL expression:

Esri Case #03574486 - How to join using Make Query Table?
…[use] SQL expressions. Please refer to the following link for examples of queries using the Make Query Table tool.
https://desktop.arcgis.com/en/arcmap/latest/tools/data-management-toolbox/examples-of-queries-with-the-make-query-table-tool.htm

topic How to join using Make Query Table? in ArcGIS Pro Questions

How to join using Make Query Table?

Re: How to join using Make Query Table?