To join tables using multiple fields to create a unique sequence where an entity say a parcel can beunique identified according to more than one criteria as shown below. This could minimize data redundancy in tables and editing time
In the ArcGIS Desktop help topic, Modeling limitations of relationship classes, please allow multiple fields to be used to define the primary and/or secondary keys that define the relationship classes, as well as in-memory relates:
The primary and foreign keys can each be based on one field only; complex primary and foreign keys are not supported in ArcGIS.
I was hoping that this would be resolved back when 9.0 came out, and was yet again disappointed seeing it didn't make it into 10.0 even.
Yes - I agree regarding the joining of tables in ArcMap - allowing multiple fields to join to multiple fields would be so much easier. There are times where I have to create a new field and calculate it to equal the concatenation of two or more other fields (and do this for both the tables I want to join). Also, sometimes my tables are in an uneditable ArcSDE geodatabase - so I first have to export the datasets to shapefile or file geodatabase to add the new concatenation fields.
What hurts is that I see support for composite keys in the XML Schema of the Geodatabase. So evidently, one could write code to use them--or it's just a stub for future development. In any case, the ability to use composite keys should be part of core and exposed via the GUI. Part of the beauty of the geodatabase, and an oft-marketed benefit, is the ability to get more donewithout having to write code.
If the feature classes/tables are in the same geodatabase or directory you can use the Make Query Table tool to do this. http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#//00170000006r000000 It only supports an inner join and is not editable without exporting it, but it can do this type of join and handle all relationship types (one-to-one, many-to-one, one-to-many, and many-to-many). MS Access is faster and supports outer joins, so if you can use a personal geodatabase you can get the result faster in that program if the data is only tabular. But the Make Query Tool creates features as well as table rows, so it is better than Access in that situation. Even so, you would be better off using the Make Query Table tool as part of a process for converting the multifield value to a single field join value to get better performance. Even MS Access performs better and offers more options with single field relationships than with multi-field relationships.
I agree I would like to join tables/feature classes using more than one field. I often find myself creating new fields that are a combination of a number of fields in the feature class just to perform a join even though this now new unique identifer field is meaningless in the real world.
