dbf-to-shp attribute join gives dofferent result than shp-to-shp attribute join.

[DionGood]

Why is it that a dbf-to-shp attribute join results in a different number of joins than a shp-to-shp attribute join of the same data? When I join the attribute table of one shapefile to another shapefile I get a certain number of joins. But when I export the table to dbf and join it the the same shapefile, I get a different (greater) number of joins. Shouldn't both routines result in the same number of joins? It is the same data, after all. Please help, this is really messing up my analysis. I am using ArcGIS Desktop.

[DanPatterson]

on the FID (aka object ID field) or are you using an attribute

multipart shapes will cause a difference.

you will need to elaborate on what fields you are joining and whether the values in both tables have unique entries for each join

[DionGood]

I am joining a shapefile of broadband deployment data aggregated to Census blocks to a shapefile of Census blocks containing urban/rural designations and households for each Census bock. The key field is not the FID field, it is the Census block number field. I don't know what you mean by a multi-part shapefile. The broadband data shapefile contains a total of 982,015 features, including multiple instances of many Census blocks, depending upon how many different providers are offering service in each, how many different technology types are deployed in each, and how many different downstream and upstream speed combinations are offered by providers in each. The Census blocks shapefile contains 519,723 features - one record for each unique Census block.

I want to find out how many urban and rural households are in Census blocks where the maximum advertised downstream and upstream speeds are at least 25mbps/3mbps. So I created a definition query in the broadband shapefile to show only Census blocks at 25/3 or greater. This reduced the size of the attribute table from 982,015 records to 654,582 records. Then I performed an attribute join of the broadband shapefile to the Census block shapefile, keeping only joined records. The join reduced the Census block shapefile from 519,723 features to 256,773 features. In other words, 256,773 of the 519,723 Census blocks in California have 25/3 broadband service somewhere in them. Then I summed the households field and got 10,289,120, meaning that there are 10,289,120 households in Census blocks where 25/3 service is offered in California. Then I created selection sets in the Census shapefile's attribute table to find out how many of the 10,289,120 households were in urban and rural areas. So far, so good.

However, upon checking my work by using a different method (exporting the definition query from the broadband shapefile to a stand alone dbf, then joining the dbf to the Census block shapefile) I arrived at the result that 25/3 broadband is available in 317,712 unique Census blocks containing 12,741,341 households. I remain shocked and perplexed, as I thought a shp-to-shp attribute join would be functionally the same in this case as a dbf-to-shp attribute join. Can you please explain why this is not the case? And which method gives the correct result without double-counting households? I am not looking to extract information from specific Census blocks, I am only interested in the overall totals of how many households are in Census blocks where ANY provider offers broadband at 25/3 or more.

Thanks.

[DanPatterson]

The order of the join is important

shp-dbf to dbf  vs dbf to shp-dbf

because

Records from the Join Table can be matched to more than one record in the Input Table.

Join Field (Data Management)—ArcGIS Pro | Documentation

and

Add Join (Data Management)—ArcGIS Pro | Documentation

one-one vs one-many

[DionGood]

Yes, I am aware of this, but in this case it shouldn't matter because I am joining a shp table or dbf with multiple instances of specific Census blocks to a shapefile with only one instance of each unique Census block. I don't care which 25/3 record from the broadband file gets joined to the Census block file, as long as the Census block file registers a join for that Census block, meaning that 25/3 exists in the block. The only way I can make logical sense of this is if NO joins occur where there are many-to-one relationships. But why would this be the case  for a shapefile table and not for a stand alone dbf? The same logic should apply.

[ABishop]

It sounds like you want a many to one relationship in your join?  To accomplish this, you could use another method of analysis called overlay.  I did this recently in ArcGIS Online.  Do you have a way to test it?

[DionGood]

Yes, I want a many-to-one relationship, but I am only interested in the tabular data, not the spatial dimension.

[ABishop]

What version of desktop are you using?  

[DionGood]

I have accepted the dbf-to-shp attribute join as the correct result, although I still don't understand why this would be. I can't consider this case "resolved" until I do. The articles were not really helpful.

[DionGood]

10.6.1