topic Re: Outer join using Make Table Query? in Geoprocessing Questions

Outer join using Make Table Query?

BarryGuidry — Tue, 20 Aug 2013 19:38:13 GMT

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?

Re: Outer join using Make Table Query?

RichardFairhurst — Tue, 20 Aug 2013 21:15:04 GMT

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?

The Make Query Table tool only supports an inner join. Outer joins are impossible with that tool. Python and a pair of cursors joined through a dictionary is your best bet.

Re: Outer join using Make Table Query?

BarryGuidry — Wed, 21 Aug 2013 11:29:31 GMT

Unbelievable. Is there no tool available to do an outer join any longer?

Re: Outer join using Make Table Query?

RichardFairhurst — Wed, 21 Aug 2013 12:59:39 GMT

Unbelievable. Is there no tool available to do an outer join any longer?

The Join tool will, on a single field, do an outer join (with limitations), but Make Query Table, which I assume you are using because a single field join won't work for what you need or because you need a one-to-many or many-to-many relationship table created, does not support an outer join. If this is actually a one field join that is in a one-to-one or many-to-one relationship, then use Make Feature Layer or Make Table View and then the standard Join tool to create an outer join. There never was an outer join tool for one-to-many and many-to-many before.

At 10.1 I have seen instances where Feature Class to Feature Class will generate a one-to-many or many-to-many output from a standard join with that relationship type, but it is not visible until after the export is complete. This is a new capability and still not sure when it operates and when it doesn't (standard Export does not seem to do this). At 10.0 this new capability is not supported.

So why were you trying to use the Make Query Table tool? What is the relationship type and number of fields you need to join on?

Python cursors and dictionaries can do this and are faster than any tools ESRI provides. See this thread. The code is new and unfamiliar for me too, but very fast once it is set up correctly.

Re: Outer join using Make Table Query?

JonathanQuinn — Wed, 21 Aug 2013 15:55:12 GMT

Outer joins should work with the Make Query Table tool:

[ATTACH=CONFIG]26856[/ATTACH]

In this case, the input polygon feature has one feature and the input table has 3 features. I set up the tool as follows:

[ATTACH=CONFIG]26857[/ATTACH]

Since I'm including the Shape field, the output will have three identical features:

[ATTACH=CONFIG]26858[/ATTACH]

Here's a blog on the topic.

Re: Outer join using Make Table Query?

RichardFairhurst — Wed, 21 Aug 2013 16:04:16 GMT

Outer joins should work with the Make Query Table tool:

[ATTACH=CONFIG]26856[/ATTACH]

In this case, the input polygon feature has one feature and the input table has 3 features. I set up the tool as follows:

[ATTACH=CONFIG]26857[/ATTACH]

Since I'm including the Shape field, the output will have three identical features:

[ATTACH=CONFIG]26858[/ATTACH]

Here's a blog on the topic.

That is not the definition of an outer join. I definitely agree Make Query Table can link up one-to-many and many-to-many relationships and create all record combinations where both tables have matching records (an inner join).

Here is what wikipedia says an outer join is:

"Outer join

An outer join does not require each record in the two joined tables to have a matching record. The joined table retains each record�??even if no other matching record exists. Outer joins subdivide further into left outer joins, right outer joins, and full outer joins, depending on which table's rows are retained (left, right, or both)."

Make Query Table cannot do an outer join. Any constraint in the criteria results in an inner join (i.e., both tables must have at least one record that matches the input criteria for an output record to be generated). An unconstrained Make Query Table join (no criteria) will associate every record in both tables to every record in the other, making a 100 x 100 record join result in 10,000 output matches. But even that is an inner join. Even if the criteria is Table A <> Table B on a field, it will create an inner join of only mismatched field values from both tables.

So using Make Query Table you cannot set up a criteria like:

Table_A.Field_A = Table_B.Field_A Or Table_B.Field_A is Null

The first half of the SQL makes sense with the Make Query Table tool, but not the second half. Since the second half makes no sense to the tool it results in no records being outputted (or else it is ignored, I forget which).

Re: Outer join using Make Table Query?

BarryGuidry — Wed, 21 Aug 2013 19:31:44 GMT

Thank you very much, guys. Mr. Quinn, I don't know if you meant to only have the SHAPE field to include from the feature class in your graphic, but I think that is the key. Previously, I had selected additional fields from my feature class, in addition to SHAPE, but this time only selected the SHAPE field to include, and it worked!

Re: Outer join using Make Table Query?

RickWarner — Tue, 02 Sep 2014 16:34:21 GMT

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.

Thanks

Re: Outer join using Make Table Query?

RichardFairhurst — Tue, 02 Sep 2014 16:52:51 GMT

Is the relationship of Table A to Table B a One-To-One or Many-To-One relationship and the same for Table B to Table C? If so the standard Join done twice may work, although I never do a two level join. I just would accept that the table cannot be directly joined this way if these are the relationships and the 2 level join does not work. However, I would work around that by joining Table B to Table C and exporting it as an outer join and then join that exported table to Table A.

If the relationship with any of the tables is One-To-Many or Many-To-Many then the relationship cannot be displayed in a table view with a Standard Join, but may export from a geodatabase to the same geodatabase at 10.1. The only way to know if the export option will work is to try it. Again the export may have to be done in two steps with the Table B to Table C export done first and then the Table A to combined Table BC exported second.

You cannot do updates in real time with a Make Query Table output from the source tables, so an export is just as good as that output if it works.

Re: Outer join using Make Table Query?

RickWarner — Tue, 02 Sep 2014 17:18:17 GMT

Thank you, that is what i will do. But I have many tables and was trying to avoid the export. Join Table A to Table B and then take Table AB and join to C and export to create Table ABC and then join to Table D ], but will get confusing after a few tables. I was hoping there was a way to join Table A which has 300k records to the other which all matach Id's but have null values.

Thanks for your quick response.

Re: Outer join using Make Table Query?

JohnTownsend — Thu, 23 Jan 2020 19:23:57 GMT

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.

Re: Outer join using Make Table Query?

RichardFairhurst — Fri, 24 Jan 2020 07:29:01 GMT

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-dictionaries 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.