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?