join error I have never seen before...

[JacquelinePursell]

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.

Cardinality Warning:

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)

[JamesCrandall]

A straight join in ArcGIS will be a 1-to-1 relationship.  You will need to create a relate, or a Relationship class in your GDB, to setup a 1-to-many rel type.

[JacquelinePursell]

I want to symbolize by the joined information and do not wish to relate.  We have similar information as a relate already (which I removed as a troubleshooting process just in case).  Also I tried to create a relate on these particular files with a similar result like I get here (only 1:1)

I have created 1:Many joins multiple times in the past with ZERO troubles. 

[JamesCrandall]

Joins are case sensitive I believe.  Maybe double-check the values for that?

[JacquelinePursell]

Checked that already as well as nulls and hidden spaces.  I even removed all formatting in the Excel prior to exporting just to be sure.  I have been having more troubles with Excel since 10.0 than I ever have, ugh!

[JamesCrandall]

You may have mentioned doing this in your OP but don't pull directly from Excel (into your gdb or map or whatever).  Save it out to a comma-delimited .csv or .txt and take a look at the values there (in TextPad/Notepad).  Then save from there again and then do your import.

Maybe I am missing the obvious, but it sounds like a tiny little difference is occuring somewhere when going between file types.

[JacquelinePursell]

I did mention the CSV.  I also just now copied the data into a new unsaved Excel and saved as a text file.  I get the same error.  I thought it was different also (that always seems to be the reason for a join error) but why would the summarize on that column show multiple matches?