2

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.

  • Please edit the question to specify the RDBMS, with version, in which you store your enterprise geodatabase, – Vince Nov 15 '16 at 17:13
  • The SQL implementation of file geodatabase is far from complete. In fact, there doesn't have to be a way to make such complex queries in SQL. You likely have two choices: Use an enterprise database and do the complex SQL there, or perform additional processing to make intermediate products available to the final query. – Vince Nov 16 '16 at 0:11
  • 1
    You can nest subqueries with IN operators referencing multiple tables. For example, Name in (select Name from Table2 where landuse in (select landuse from Table1 where sec_no = '001')) – klewis Nov 16 '16 at 1:33
  • Hey @klewis, thanks for your help. I was able to take your suggestion and re-work it to fit my case. – Sethdd Dec 14 '16 at 20:51
  • @klewis It looks like your comment is suitable to post as an answer that the asker would accept. – PolyGeo Jan 25 '17 at 8:06
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.