idea Grant ability to select based on related tables (inner join, outer join) in ArcGIS Pro Ideas

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

MarcusVowell_Uniti — Thu, 17 Mar 2022 16:18:49 GMT

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.

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

jcarlson — Thu, 17 Mar 2022 14:53:03 GMT

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.

Re: Grant ability to select based on related tables (inner join, outer join) - Status changed to: Needs Clarification

TanuHoque — Thu, 17 Mar 2022 16:19:28 GMT

@MarcusVowell_Uniti

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

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

BruceHarold — Thu, 17 Mar 2022 16:27:57 GMT

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.

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

TanuHoque — Thu, 17 Mar 2022 16:35:25 GMT

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 😛

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

BruceHarold — Thu, 17 Mar 2022 16:40:51 GMT

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.

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

MarcusVowell_Uniti — Thu, 17 Mar 2022 17:13:44 GMT

@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.

Re: Grant ability to select based on related tables (inner join, outer join) - Status changed to: Open

TanuHoque — Fri, 01 Apr 2022 15:56:41 GMT

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

TanuHoque — Fri, 01 Apr 2022 16:00:38 GMT

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

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

TanuHoque — Fri, 01 Apr 2022 18:05:31 GMT

@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)

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

TanuHoque — Fri, 01 Apr 2022 20:46:01 GMT

@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-layer.htm

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

BruceHarold — Mon, 04 Apr 2022 15:53:04 GMT

I find that SQL statements for the use case can blow up on you when row counts are in the hundreds of thousands, for example leveraging the output of Generate Near Table.