[GIS] Building a Definition Query using Sub-queries with multiple conditions

arcgis-10.4arcgis-desktopdefinition-queryfile-geodatabasesql

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.

enter image description here

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.

enter image description here

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.

enter image description here

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:

enter image description here

In a nut shell, this is what the query does:

  1. First Select Statment: Select records in my Composite table that have values in common in the field PAMS_PIN.
  2. Second Select Statement: From the selected records in the composite table, select records from the activities table that have values in common in the field PI_Number and...
  3. Where Clause: Of the selected records in the activities table, select the records that based on the field, Activity Class Code, equals a specific value.
Related Question