topic Re: Joining with 1:M relationship in Data Management Questions

Joining with 1:M relationship

VincentOkotel — Thu, 05 Aug 2021 08:51:48 GMT

HI,

Kindly help me.

I am trying to join tables with a one to many relationship.

I need to symbolise using the data in the many relationships (stand alone table) but it can't. How best can I navigate that. Have tried and have failed.

When I join, I lose data in the output table and my results end up not being significant.

I will be so grateful for your kind help.

Re: Joining with 1:M relationship

EduardoFernandez1 — Sun, 15 Aug 2021 23:54:47 GMT

Hi Vincent

I assume you have a feature class ( 1 feature) to join to a table ( Many rows) in an enterprise geodatabase? You can use a database view with a right join to result in 1 feature (overlapping) linked to each row from the table.

If its between two tables, you could also try Joining attributes in one table to another—ArcMap | Documentation (arcgis.com). This page might also help Essentials of joining tables—ArcMap | Documentation (arcgis.com)

Regard Ed

Re: Joining with 1:M relationship

Vincent_Okotel — Fri, 20 Aug 2021 17:25:56 GMT

Thank you Edwardo Fernandez for your response.

Yeah, when i use the right join it works for symbology.

However I cant do further analysis like analysis like logistic regression because the IDS are no longer unique.

This is the error message it brings

Generalized Linear Regression (GLR)
=====================
=====================
(Error) Messages

Start Time: Friday, August 20, 2021 8:21:21 PM
WARNING 001605: Distances for Geographic Coordinates (degrees, minutes, seconds) are analyzed using Chordal Distances in meters.
ERROR 000644: Duplicate IDs found in OBJECTID.
ERROR 000643: Invalid unique ID field.
Failed to execute (GeneralizedLinearRegression).
Failed at Friday, August 20, 2021 8:21:38 PM (Elapsed Time: 16.86 seconds)

How can I then do 1:M join while maintaining unique object IDS

Re: Joining with 1:M relationship

Eduardo_Fernandez_EsriAu — Sat, 21 Aug 2021 06:53:25 GMT

Hello Vincent

Ok, I see the issue now. Object ids are unique in the underlying enterprise geodatabase artifact but not in the view. Depending on your relational database management system (Microsoft SQL, Oracle etc), you could try, for example, Create View AS <viewname>, Select ROWNUMBER() As ID, or NEWID() As 'id' ? This adds an artificial identifier for row number in the view representation and does not represent a unique identifier for rows from the underlying source tables. My understanding is that this kind of identifier cannot be used as a primary key or be in a foreign key relationship between this View and other artifacts. Worth a try.

If that doesn't work, then the only other alternative is to create the desired artifact before the linear regression analysis can occur. Use Add Join geoprocessing tool on two layers and then use the Copy Features or Feature Class to Feature Class tool to export into a new artifact. The new artifact would then have unique object ids for each row. Use a Model (Model Builder) to script this workflow to run manually as required or schedule it to run at a time and frequency that suits.

Add Join (Data Management)—ArcGIS Pro | Documentation

Re: Joining with 1:M relationship

Vincent_Okotel — Tue, 24 Aug 2021 07:08:52 GMT

Thank you so much EduardoFernandez. I used copy features and it worked out for me.