How to create an SDE view with a 1 to many relationship?

Question

Hello everyone,

Is there a way to create an SDE view using a FC and a table, where for each record in the FC there are 0 or several records in the table.

I tried to create a SDE View i arcCatalog 10.2 with a query similar to this one:

select FC.Code, Table.DateEvent from FC, Table where FC.Code  = Table.Code

and when I add the SDE view in ArcMap it asks for an field that is a unique ID, otherwise the SDe View can not be shown.

How can I create a field that is an unique id.

Thanks

JoseSanchez · Answer

Finally we used a Query Table

https://community.esri.com/thread/183385-timeslider-and-one-to-many-relationships

https://blogs.esri.com/esri/supportcenter/2012/05/10/a-quick-tip-on-performing-a-1m-join/

Thanks

JoseSanchez · Answer

Thank yo for your answer Joshua,

I think this post answer my question: Choose a unique identifier field for a query layer—Help | ArcGIS for Desktop

Yes the OUTER JOIN goes from the table to the FC. Selecting the field ObjectID from the table as a the unique ID field.

Other solution is to export the SDE view to a FC then the field OBJECTID gets added.

Thanks

JoshuaBixby · Answer

Have you read the Views in an enterprise geodatabase documentation?  It does a good job of laying out different qualifications, constraints, footnotes, etc... about using views with an enterprise geodatabase.

When the SDE command line tools went away, registering views in a geodatabase went away with them.  Starting with ArcGIS 10.5, views can once again be registered with a geodatabase:  Register a table or view with the geodatabase.  Until you migrate to ArcGIS 10.5, there is no way to avoid the prompt you speak of because there is no way to store metadata about the view, e.g., the unique ID field, within the geodatabase.

In terms of SQL, you will need to do an OUTER JOIN, either left or right depending on how you want to write the SQL.  There are countless SQL tutorials online that can explain and show how OUTER JOINs work, so I won't get into that here.