Joining with 1:M relationship

405
4
Jump to solution
08-05-2021 01:51 AM
Labels (1)
VincentOkotel
New Contributor
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. 
0 Kudos
2 Solutions

Accepted Solutions
EduardoFernandez1
Esri Contributor

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

View solution in original post

0 Kudos
EduardoFernandez_EsriAustralia
New Contributor II

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

Ed 

View solution in original post

0 Kudos
4 Replies
EduardoFernandez1
Esri Contributor

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

View solution in original post

0 Kudos
Vincent_Okotel
New Contributor

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 
0 Kudos
EduardoFernandez_EsriAustralia
New Contributor II

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

Ed 

View solution in original post

0 Kudos
Vincent_Okotel
New Contributor

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