I just used sql server's sql profiler to see what queries are actually issued by the esri query screen against this service. The hope was I could figure out what text string to pass it so that it could correctly filter on a field's value.
I'm guessing that this query issuing code was done back in ancient times when dbase II was brand spanking new and there were all kinds of technical limitations to work around. Either that or someone was taking some controlled substances.
I'm going to try building a view in the database and point the service to that instead. If that doesn't work (at all or fast enough), I'll just denormalize the data into a single table.
I'm no longer expecting an answer that shows how to make the query work.
(But I would love to be surprised.)
