Joining with 1:M relationship

Question

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.

Eduardo_Fernandez_EsriAu · Accepted Answer

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

Eduardo_Fernandez_EsriAu · Accepted Answer

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