Export of a joined table results in more features than you start with

[Anonymous User]

Original User: seckhardt

In ArcGIS 10.1 my export of a joined table has more records than the original feature class from which it came.  It took me a bit to figure out but it looks like if you have a duplicate in the field in which you are joining to, say a table for instance, the export feature class of the joined data will create a duplicate polygon in your feature class.  

For instance, let�??s say you have a polygon feature class with just 1 record and you join this data to a table of 5 records that is a duplicate of each in the join field you are using.  After the join you still have 1 record in your feature class table. However, I'm finding that when you export the joined data, I'm now left with a feature class containing 5 records where it made a duplicate polygon to match the number of records in the table.

I can't see why this would be intentional and is definitely not the output you get prior to ArcGIS 10.

[JasonCleaver]

You are not alone.  I am seeing the same thing in 10.0sp5.

In ArcGIS 10.1 my export of a joined table has more records than the original feature class from which it came. It took me a bit to figure out but it looks like if you have a duplicate in the field in which you are joining to, say a table for instance, the export feature class of the joined data will create a duplicate polygon in your feature class.

For instance, let�??s say you have a polygon feature class with just 1 record and you join this data to a table of 5 records that is a duplicate of each in the join field you are using. After the join you still have 1 record in your feature class table. However, I'm finding that when you export the joined data, I'm now left with a feature class containing 5 records where it made a duplicate polygon to match the number of records in the table.

I can't see why this would be intentional and is definitely not the output you get prior to ArcGIS 10.

[Anonymous User]

Original User: melanie_summers

Hello,

It sounds like you are running into this NIM. This was rejected as by design, the reason being that outer joins are now executed in the database.

One was to get around this would be:

Export FC to shapefile > Export table to dbf > conduct join > export joined shapefile to new shapefile > import into file or personal GDB

Best,
Melanie S.

[Anonymous User]

Original User: seckhardt

Melanie, you mentioned 'one way to get around this' below.  What would be another as conversion to a shapefile would not be an acceptable solution since it would ruin the geomerty of the arc segments.  Also I can not access the link you refer to as it will just ask me to login ever after I am already logged in.  I'm assuming this is a link only for internal personnel.  Do you have any other information you can point me to regarding this?  Thank you.

Hello,

It sounds like you are running into this NIM. This was rejected as by design, the reason being that outer joins are now executed in the database.

One was to get around this would be:

Export FC to shapefile > Export table to dbf > conduct join > export joined shapefile to new shapefile > import into file or personal GDB

Best,
Melanie S.

[RichardFairhurst]

Since you apparently do not care how the One To Many relationship would be handled (since it was arbitrary what joined record was kept from a One To Many relationship prior to 10.1), run the Summary Statistics tool on the full output using the Unique ID of the original unjoined features and get the Min summary of some unique ID from the joined data (OID_1?).  Join that summary table output to the full set of exported features on the feature Unique ID and select all records which match the Min Join Table Unique ID.  Remove the summary table join and export the selection to a geodatabase feature class.

Or else Summarize the joined data using Summary Statistics so that it will only constitute a One to One relationship to the features prior to your join and export.  If you care about retaining a particular record association from the joined table you would have to process the table to extract that record for each feature using a similar technique to the one I mentioned for your full export.

[Anonymous User]

Original User: kimo

I can think of many better ways of avoiding the expansion than creating a shapefile and dBase table!

1 Your could use the tool MakeQueryTable http://resources.arcgis.com/en/help/main/10.2/index.html#//00170000006r000000which allows a more sophisticated SQL query to manage the type of join. There are limitations with this tool, the most difficult is that all the tables in the query must be in the same geodatabase, so you might have to make a temporary copy.

2 You could suppress the duplicates before you join the table with a definition query.

3 Run a frequency on the fields you want to join, assuming they are the same for each key. Otherwise you would have random data anyway.

4 You could add the extra fields yourself and run a cursor to populate the extra values. Use a dictionary to extract the values from the table you would have joined for speed. That is, do NOT open two nested cursors and run a cursor n times inside the other cursor. Using a dictionary and cursor is often the fastest for very large tables by a factor of 10.

5 Use the Data Interoperability Extension aka FME to do the join where it is easy to handle duplicate suppliers.

Personally I avoid an ArcMap join because of the problem you are running into and the low performance when processing joined tables.

[RichardFairhurst]

I can think of many better ways of avoiding the expansion than creating a shapefile and dBase table!

1 Your could use the tool MakeQueryTable http://resources.arcgis.com/en/help/main/10.2/index.html#//00170000006r000000which allows a more sophisticated SQL query to manage the type of join. There are limitations with this tool, the most difficult is that all the tables in the query must be in the same geodatabase, so you might have to make a temporary copy.

Since coming up with the same number of features as you started with is important it should be noted that any unmatched records between the two tables will not show up if you use the Make Query Table tool.  There is no setting similar to the Keep All Records option that you have with a normal join.