idea Joins based on multiple fields in ArcGIS Pro Ideas

Joins based on multiple fields

CharalambosMattheou4 — Fri, 25 Jun 2021 18:30:05 GMT

To join tables using multiple fields to create a unique sequence where an entity say a parcel can be unique identified according to more than one criteria as shown below. This could minimize data redundancy in tables and editing time

Re: Joins based on multiple fields

DEWright_CA — Thu, 20 Jan 2011 17:46:00 GMT

I can't count how many times I needed to join data based on more than a single value to get my view to work. This really would be a great enhancement.

Re: Joins based on multiple fields

LindaLilburne — Fri, 25 Nov 2011 02:49:43 GMT

Absolutely essential. If one doesn't have write access then one can't do the workaround of combining the fields into a single field.

Re: Joins based on multiple fields

RichardFairhurst — Fri, 25 Nov 2011 07:00:05 GMT

If the feature classes/tables are in the same geodatabase or directory you can use the Make Query Table tool to do this. http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#//00170000006r000000 It only supports an inner join and is not editable without exporting it, but it can do this type of join and handle all relationship types (one-to-one, many-to-one, one-to-many, and many-to-many). MS Access is faster and supports outer joins, so if you can use a personal geodatabase you can get the result faster in that program if the data is only tabular. But the Make Query Tool creates features as well as table rows, so it is better than Access in that situation. Even so, you would be better off using the Make Query Table tool as part of a process for converting the multifield value to a single field join value to get better performance. Even MS Access performs better and offers more options with single field relationships than with multi-field relationships.

Re: Joins based on multiple fields

TomazSturm — Thu, 08 Nov 2012 13:48:59 GMT

Absolutly essential. This would make my work faster and easier.

Re: Joins based on multiple fields

Bud — Fri, 28 Oct 2022 17:05:08 GMT

Here's a technique that might help in some cases:

If we want to edit a table while filtering based on a multi-field join to a table, we could use a subquery and EXISTS:

(in either a Definition Query or the Select By Attributes window)

exists (
    select 
        null
    from 
        related_table
    where                                                   
        --this is the equivalent to a multi-field join:
        main_table.event_id = related_table.event_id and main_table.year_ = related_table.year_ 
    )

https://www.techonthenet.com/sql/exists.php

The downside is that the multi-field join via the subquery only filters the rows. We can't use it display columns in the Attribute Table from the related table.

Alternatively, if we want to display columns in the Attribute Table from the related table, we could create a database view that includes the data from both the main table and the related table. And do a multi-field join via SQL in the view.
And either use/view it as-is in ArcGIS (read-only). Or in ArcGIS, join it to original table and edit the table -- while being able to see the columns from the related table.

Re: Joins based on multiple fields

MiguelMartinezYordan — Thu, 15 Jun 2023 15:12:58 GMT

Please add the Joins based on multiple fields tool. It will be a tool where you will have the main table and the join table, each with the options to select primary, secondary, tertiary, and so on field options so that the primary field from the main table will match the primary field form the joint table and so on. This will eliminate the need to filter values prior to a join when multiple columns are needed.

A good example is when you have a main column with three categories, and a second column with subcategories, bot those subcategories can be repeated between main categories. Let say you have a main column with building names and a second column with apartment numbers. An apartment number 5B can exist in more than one building. But you want to update only apartment 5B from one specific building. That's where the Joins based on multiple fields tool will filter first the building names and second the apartment numbers.

Joins based on multiple fields

MiguelMartinezYordan — Wed, 04 Oct 2023 20:25:29 GMT

Re: Joins based on multiple fields

Bud — Thu, 11 Jan 2024 21:31:54 GMT

A last resort option might be to concatenate multiple join fields together into a single string. And then join on that one concatenated field rather than multiple fields.

But that's pretty messy. I my case, I wanted to concatenate a string and a date together in a file geodatabase view. But I couldn't get that to work using the CONCAT() FGDB function.

Re: Joins based on multiple fields

Bud — Fri, 12 Jan 2024 07:22:12 GMT

@RichardFairhurst

Good call on Make Query Table.

It's too bad it doesn't let us join on date fields. I wanted to join two file geodatabase tables on SPECIES=SPECIES and DATE=DATE. But I don't see my date fields in the Key Fields parameter picklist, so best guess is date fields are not supported for joins.

Make Query Table — Use date field in join

Re: Joins based on multiple fields

DougGreen — Fri, 24 Jan 2025 16:16:22 GMT

I need this as well. I'm considering creating a custom tool for it. I have a frequent need to join measurement records to a point feature class. The point features have siteid and genid. They are dual keyed. To join measurements, I have to do it with python currently. It would be great to be able to just specify more than one join field, similar to what can be done in a sql join statement. I like your idea @MiguelMartinezYordan .

Re: Joins based on multiple fields

Bud — Fri, 17 Jan 2025 00:20:54 GMT

Relate using multiple fields

Re: Joins based on multiple fields

Bud — Sat, 09 Mar 2024 00:36:39 GMT

As a last resort, you could create a COMPOSITE_ID field. Populate via an attribute rule by concatenating the apartment number and building name together into a single text field. Do that in both tables. Create a single-field join using Add Join on the COMPOSITE_ID fields.

Re: Joins based on multiple fields

Phillip_Cobb — Wed, 15 May 2024 19:14:57 GMT

For the date field could you convert into a numerical field such as 20240308 for both and see if that will work…