Solved! Go to Solution.
I have tried creating an outer join using the "Make Table Query" geoprocessing tool, along with "Copy Features", in Modelbuilder to join a table to a feature class (within the same PGDB), but am getting mixed results each time. Additionally, records are being dropped somehow, for no apparent reason. I followed the suggestion from an old thread here, but using ArcGIS Desktop 10.0. Can anyone advise how to correctly use the "Make Table Query" tool?
Unbelievable. Is there no tool available to do an outer join any longer?
Outer joins should work with the Make Query Table tool:
In this case, the input polygon feature has one feature and the input table has 3 features. I set up the tool as follows:
Since I'm including the Shape field, the output will have three identical features:
Here's a blog on the topic.
Thank you for this clarification Richard. I too have been struggling with the outer join dilemma. Have you created an idea at all for this to suggest providing more support for join types?
I have datasets set up to be built as relational geodatabases, but the query functionality that I have used within Arc has its limitiations. I have been able to get creative using query tables, table views, and in memory joins, however, in-memory joins cause certain processes to fail such as packaging maps.
The suggested solution by many is to export the features into a new feature class, which completely violates the whole concept of having a relational database in the first place. I'll continue to experiment with this concept, but from what I can tell, there is limited functionality with data queries.
Since the release of AcrMap 10.1 I only use joins that involve One to Many or Many to Many relationships when I store all tables in the same Geodatabase and export them with the joins in place. If a scheduled scripting approach is acceptable for solving my problem, I have used the technique in my /blogs/richard_fairhurst/2014/11/08/turbo-charging-data-manipulation-with-python-cursors-and-diction... blog to combine multiple features/tables in memory to generate either inner or outer join forms at my discretion and output them. The code can get tricky as you need to employ different embedded for loop code combinations and json like structures to get the different join effects, but the performance is excellent. In many respects the code resembles the requirements of Access to build up the joins using multiple chained queries if your inner and outer join relationships become too ambiguous to do in a single query as you add more and more tables. However, I have not discovered a visualization solution that responds to edits in the source normalized data in real time when One to Many or Many to Many relationships are involved. At the same time, I find that Access often requires me to hit the F5 key to refresh the query when I use these kinds of chained queries, so in theory a similar manual refresh approach from an addin toolbar could trigger a script based on my blog to get updates from the source data in near real time and output to an inmemory or stored feature class displayed in a layer. I would have to have a use case laid out for me and time to experiment with it to see how well that would work.
This is the only post I found on this topic so I'll give it a shot,
I have several tables in a files geodatabae,
Table A has all the id's, say 300k records, I would like to do a full outer join on say three tables, some will have 130k matching Id's, Table C has 40 matchng Id's.
So that I have a join that displays all 300k records?
is there any way to do this in Arcmap 10.1, full license?
Or another program, I've tried access and they will support a table join of A & = B, but not A & B & C.