Background
Since layer selections can't be be published as a map service, I need to duplicate N layers and write definition queries for each to satisfy my needs. I can't create new standalone layers because of multiple relationships this feature class participates in (multiple M-N and 1-M relationships) otherwise I would have created a python script to take care of this. Here is a flowchart of my tables and feature classes. Tables/features classes are stored in a file geo-database (ArcGIS 10.4) for this particular example.
My question will focus on selecting features from the Parcel feature class based on records in the Activity Record ID Table.
Question
I’m trying to create a definition query using sub-queries with multiple conditions but running into some issues and not sure if SQL statment approach is correct.
I can create a subquery that selects parcels based on the parcel ID's in the activity record ID Table table class. This basically selects all the parcels with Parcel ID's that are present in the Activity Record ID Table.
I want to refine the sub-query further by only selecting parcels from the activity table for a specific activity in that table. This is what I have so far but arcmap crashes as soon as apply/test the query.
I'm not sure if the SQL statement is correct but there has to be a way to create sub queries for with multiple conditions.
Best Answer
Based on some insight from @klewis, I was able to write my definition query:
In a nut shell, this is what the query does:
PAMS_PIN
.PI_Number
and...Activity Class Code
, equals a specific value.