Trying to perform “relate” in Model Builder

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.

Sample of a Table View using a subquery

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.

5 thoughts on “Trying to perform “relate” in Model Builder”

  1. This is a really useful tip as I’ve been wanting to do a “relate” in model builder for ages. Basically I’ve got to learn more SQL!

  2. I came back to this post as I had forgotten how to do this, but some how remembered which site to go to! Anyway have now added your RSS feed to Thunderbird so I should not miss any more of your top tips!

  3. I am attempting the same problem although it doesn’t appear to work. I have two datasets, a feature class and a table. I query on the feature class first in my model. WIth this selected, I’m attempting to use the ‘Make Table View’ tip to select all related records in the table. The relationship is one to many. Any thoughts would be extremely helpful!!

Whatcha Think?