I was using Model Builder (ugh!) to select records in one table (CWI.C5ST) that relate to a subset of records ([BHGEOPHYS] = ‘Y’) in another table (CWI.C5IX). There is not an existing tool for doing this in ArcGIS. I did find a post by Layne Seely in ArcForums titled “trying to perform “relate” in Model Builder.” that led me to the Make Table View under Data Management Tools-Make Table View and even had the basic syntax I was looking for (if it hadn’t I probably would have guessed that it would not allow subqueries).
Since I always like to see exactly the syntax someone uses, I decided to show the syntax I used in case that helps anyone else.
It does require that the tables reside in the same workspace, which is a reasonable limitation that I can live with. Another drawback is that if you export the model to a python script, the command can get pretty long, especially if you have many fields.