The number of matching records for the join:
- 171 of 579 records matched by joining [WATER_ID] from <AssessedStreams2012> with [ASSESSMENT_UNIT] from <ImpairmentCauses2012>.
Matching records may not appear in table view due to join validation errors.
Join Validation detected 379 non-distinct matching records when using [WATER_ID] from <AssessedStreams2012> with [ASSESSMENT_UNIT] from <ImpairmentCauses2012>. For geodatabase data, ArcMap will join all matching records, although only the first matching record will be displayed in the attribute table of <AssessedStreams2012>. This relationship is 1:Many or Many:Many. When working with 1:M and M:M relationships, it is recommended that relates or Relationship Classes be used instead of joins. Refer to the ArcGIS Desktop Help topic: About joining and relating tables for best practices on creating relationships between tables.
This is the error I get when validating a join with 2 of the (what I thought was the same) ID columns (Water_ID to ASSESSMENT_UNIT) in a 1:Many. One is a line feature class (AssessedStreams2012) and the Many is a table (ImpairmentCauses2012) in the same file database. The ImpairmentCauses2012 table was originally from Excel and my coworker who brought it it had gone through some weird step to DBF then the geodatabase, which I thought was the cause, but I imported it again straight from Excel as well as stepping through a CSV and neither fixed the issue.
I ran a summarize on both the ID's to look more closely at the values. The table with the the Many in it is showing me multiple records of the same ID (1-8 values), so I find it odd that when I run the join, it only joins the first value of the match rather than all 5 or 8 matching records. The ID's are in this format: NV06-SC-64_00. There are no NULL values in either table and no hidden spaces or special characters in the column headings. I also checked that the file path contained no spaces or special characters either. Both columns are Text 255 also.
I am using 10.2.2 ArcInfo (Advanced)