Grant ability to select based on related tables (inner join, outer join)

[MarcusVowell_Uniti]

While there are roundabout ways to make this happen, it would be nice to be able to alter not only the WHERE clause, but also the FROM clause so that we can incorporate inner and outer joins to other layers and tables. Or, at the very lease, when a relate is established between tables, allow the fields from the related tables to show up in the Select by Attributes and other query builder dialogs.

[jcarlson]

Have you checked out Query Layers? You can do all of that with a query layer. If it's things like shapefiles, web layers, etc., that's what the ad-hoc "add join" and "add related" tools are for in the map.

When there is a real relationship class in the database, you ought to be able to see related records for a selected feature in the Attributes pane.

[TanuHoque]

@MarcusVowell_Uniti 

would you mind add some of your use cases/workflows here? thanks

[BruceHarold]

This is the old Keyfile select capability from Arcplot, it is very much needed in Pro.  I have emulated it with a script tool but its not ideal.

[TanuHoque]

oh gosh! @BruceHarold I can't seem to recall that arcplot use case. You have anything that I can use to juggle my old memory 😛 

[BruceHarold]

Like this:  https://pm.maps.arcgis.com/home/item.html?id=e638afe0695a4ad38388cb8d9b350446

Selections are propagated based on shared values of two fields, one in each layer/view. 

[MarcusVowell_Uniti]

@TanuHoque

For instance, a geospatial table of fiber optic cable is foreign keyed to a table listing leasing agreements that have a customer ID listed in each record. That customer ID is, in turn, foreign keyed to a customers table. I need to select all cables a specific customer by name is on. With SQL, I would do this with this query:

SELECT cable.* FROM cable INNER JOIN agreements ON cable.agreementid = agreements.id INNER JOIN customer ON agreements.customerid = customer.id  WHERE customer.name = 'AT&T'

Many of the databases we use have a chain of foreign keys that need to be used to select the right subset of data. In fact, what I would dearly love to see is the ability to utilize nested SELECT statements in the WHERE clause:

SELECT * FROM cable WHERE ownership NOT IN (SELECT id FROM owner WHERE status = 'Inactive')

But I know that's probably a long shot. Just being able to consider more than one table in a SELECT criteria would be fantastic.

[TanuHoque]

Thanks @BruceHarold and @MarcusVowell_Uniti for providing clarifications. They are very helpful.

[TanuHoque]

@MarcusVowell_Uniti 

Many of the databases we use have a chain of foreign keys that need to be used to select the right subset of data. In fact, what I would dearly love to see is the ability to utilize nested SELECT statements in the WHERE clause:

SELECT * FROM cable WHERE ownership NOT IN (SELECT id FROM owner WHERE status = 'Inactive')

But I know that's probably a long shot. Just being able to consider more than one table in a SELECT criteria would be fantastic.

Technically you can do it today. We do not have any UI that allows you to construct that kind of where clause with subqueries but you can simply switch to the SQL mode and write your sql in there.

Here is a screenshot where I selected airports that has runways (in a separate table) longer than or equal to 10,000 ft.

Of course, you use do the same in the Definition Query page to filter them out instead of selecting them.

There are two version of the sql I'm providing here:

-- option#1
exists (select 1 from AIRPORT_RUNWAYS r where r.LENGTH >= 10000 and AIRPORT_FACILITY.FKOBJECTID = r.FKOBJECTID)

-- option#2
FKOBJECTID in (select FKOBJECTID from AIRPORT_RUNWAYS r where r.LENGTH >= 10000)

[TanuHoque]

@MarcusVowell_Uniti 

As @jcarlson said above, you can also use query layer if you want.

Query layer can have parameters too. And that is how you can update values in a where clause without re-create (or updating the source of) the query layer. See screenshots below.

As you know these queries can be very complex.

Here is the help on query layer parameters. https://pro.arcgis.com/en/pro-app/latest/help/mapping/layer-properties/define-parameters-in-a-query-...