arcgis desktop - One-to-many joins on a feature class to a table

I am using ArcMap 10.2.2 with an Advanced license. I have used ArcMap for other basic analyses, but am fairly new to the program and have only basic coding skills, with no SQL experience outside of very basic queries using the SQL code builders in ArcMap.

I have polygons of species' geographic ranges and I have a table with a list of pathogens/diseases carried by those species. Certain species carry multiple pathogens. In an ideal world, I'd join the polygons of the ranges to the table of the species+pathogens and keep the polygon/spatial data, but it doesn't seem like there is a GIS tool to do this (I am going to later count overlapping pathogens, so it's important that each species-pathogen combination has its own feature, rather than one feature that has the information for multiple pathogens). These are what my data look like (#1 & #2, #3 is for later):

Attribute table for spatial features:

Table without any spatial data:

(Please note that these are not real data, and the pathogen-species combinations may not be real. I actually have 115 polygons and 519 table rows. Species carry anywhere between 1-40 pathogens and thus species have anywhere between 1-40 rows in my table)

As an added bonus, because of future analyses I need to do, I would ideally like to be able to do this one-to-many join such that I could keep an extra column of data from the table (see below for an example of how this might look). However, this is less important because if I can figure out how to do the join, I can just redo the analysis.

There are a few solutions that I found to this problem, but they didn't work for me for reasons listed below:

Solution 1: David Aalbers's blog - I am too much of a novice to figure out how to edit the newer script and figure out how to import it into ArcMap. I tried the older script and it just generated a ton of empty polygons titled test, test_1, test_1_1, test_1_1_1, etc.

Solution 2: One-To-Many Join - I tried to use the Make Query Table tool (and I have no SQL knowledge, although I used the expression builder) and I got ERROR 000383: Issue with a table, cannot find the workspace & Failed to execute (MakeQueryTable). All my files are in the same folder, but they are not in a geodatabase.

Answer

To multiply the features do this (available to those who have ArcGIS 10.1 or above).

1. Place the features and table into the same file geodatabase (you must convert Shapefiles/Excel/DBF files into the geodatabase for this to work).


2. Make sure your polygons have a unique ID field that will be preserved (you can create a Long field and calculate the ObjectID into it so that the ObjectID value will not be lost).


3. Do a standard Join of the table (attributes, no spatial data) to the features (spatial data) on the matching field. Note that the attribute table of the features (spatial data) will have the same number of features that it did before, but don't worry about this, they all show up in the next step.


4. Export the features to the same geodatabase as a new feature class (right click the layer from the table of contents and choose export). Note that the number of features in attribute table of the exported features now has the right number of features, the same as the original table (with attributes, no spatial data) or, in the case of a many to many join, a complete set of features for all combinations of features and related records.

The features will multiply in a new feature class where every feature will be present (even if it did not have a match in the table) and where there was a 1:M match before, the features will be multiplied to make a 1:1 match of each feature to all of its table entries. The Unique ID field you created in step 2 will allow you to relate the multiplied polygons to the original single polygon set. With the new feature class of multiplied features you can do selections on the table fields, summaries, Spatial Joins, etc.

If you want to generate a unique ID value that represents each unique species and disease combination you can use the 10.2 version of the tool described in this Blog post. Having this type of key lets you use it to do standard joins to summary tables and statistics based on a multi-field relationship as a substitute for creating an actual multi-field relationship using a Make Query Table set up.

Here is a good reference from esri: https://support.esri.com/en/technical-article/000001228