Multi field many to many relationship

Gerald_ZBray — Thu, 01 Dec 2011 20:09:15 GMT

Hoping some can point me in the right direction. I am trying to build a relationship that will allow me to see which transformers associated with a transformer bank support a service location based on the phase of that location.
I have something in SQL:
"Select a.CustName, a.Phase, a.TransformerBank, c.TransformerId, c.TransformerPhase
From ServiceAccountTest a
Join PhaseMap b On a.Phase = b.EndPointPhase
Join TransformerFeatureTest c On a.TransformerBank = c.TransformerBank and c.TransformerPhase = b.FeaturePhase"

The PhaseMap table is:
EndPointPhase FeaturePhase
------------- ------------
1             1
2             2
4             4
3             1
3             2
5             1
5             4
6             2
6             4
7             1
7             2
7             4
3             3
5             5
6             6
7             7

This give me the results I want, but I would like for something in the geodatabase that would allow the Identify and other tools to follow the relationships out.

Anybody got any ideas?

Re: Multi field many to many relationship

Gerald_ZBray — Wed, 28 Dec 2011 12:18:27 GMT

Ok, I think I have sorted this out. I had orginally wanted to do all of this in the GeoDatabase and through ArcCatalog, but you can't. Here is how I solved it.
I created 2 multiversioned views using the sdetable command. Note this is a command prompt tool, not a database stored procedure. See http://help.arcgis.com/en/geodatabase/10.0/admin_cmds/support_files/datamgmt/sdetable.htm for the syntax and how to use the command. You will need to include the service, database, server, user and passwords when using the command.

Once you have the multiversioned views pointed at the correct version. You can create a normal database view. I did:
SELECT     GIS.ServiceLocation_mv.ServiceLocation, GIS.PHASEMAP.FinishPhase, GIS.TransformerFeature_mv.PRIKEY
FROM         GIS.ServiceLocation_mv INNER JOIN
                      GIS.TransformerFeature_mv ON GIS.ServiceLocation_mv.XFRKey = GIS.TransformerFeature_mv.PRIKEY INNER JOIN
                      GIS.PHASEMAP ON GIS.ServiceLocation_mv.PhasingCode = GIS.PHASEMAP.StartPhase AND
                      GIS.TransformerFeature_mv.PhasingCode = GIS.PHASEMAP.FinishPhase
The ServiceLocation field is my pointer into one table and the PRIKEY field is my pointer into the other.

Once the view is in place you will need to use ArcMap to configure the mxd with the relate information. I added the view to the mxd, not required but allows you to define the display field. I then created two relates one form my ServiceLocation feature into the new view and one from my TransformerFeature into the new view.
Put all together this give me the result I was looking for.

topic Re: Multi field many to many relationship in Data Management Questions

Multi field many to many relationship

Re: Multi field many to many relationship