First, I apologize if this is a repeat question but I searched the forums for quite a while with no luck.
I have a table stored in SQL that has ~9600 records where each row represents 3 possible factors for a county. I have a geography layer of counties (also in SQL) for the US (~3200 records). I then apply a definition query to the table so that it only has a certain subset of the records. I then join the table to the geography file and everything works fine. I open the table and there are ~3200 records with all the correct data.
I can then use the 'select by attribute' tool and select a field in the joined table and the query highlights the subset of the table. However when I try the 'select by attribute' tool on a field in the geography file the query returns nothing selected. For this example, I have the state names in the geography file and want to select all of the records for a single state, the query cannot find this, however if I put the state name into the joined table and do the exact same query it finds the subset.
There are indexes created on both join fields. there is an ObjectID field in both the table and geography layer, both are stored in SQL. I am working in 10.2 on Windows 7 machines. The query by attribute also works perfectly fine if I remove the definition query from the join table, however the data is incorrect because the join is no longer on the correct attribute. It somewhat acts like it cannot add an additional where clause to the selection when dealing with the geography file but it can when dealing with the join table.
As it is impractical for me to alter all of the data tables we have can anyone help me identify what is causing the issue and preventing me from returning queried results from the geography layer?
One-to-many relationships are not supported by table joins. So the definition query based on that kind relationship won't work reliably by design. The Make Query Table tool is designed to deal with one-to-many relationships and is how you should set this up. One to many relationships in ArcGIS require you to do things that are not practical, but they are necessary to make it work.
If you export the join without a definition query the features will be duplicated to create 3 copies of the counties joined to each one of the 3 rows.
Alternatively dividing the one table into 3 tables and using 3 separate joins would also work.