I work with a water well data with a related table of lithologies. So a given well can have 1 or many related lithologies. The WELLID is a unique for a given well. 70000000017 has 4 lithologies where 70000000018 has 2. Each lithology can be either glacial drift (AQTYPE=D) or bedrock (AQTYPE=R). Some wells never tagged bedrock so they have all AQTYPE=D. Others have generally the upper part as AQTYPE=D and the lower part as AQTYPE=R. I am trying to write an ArcGIS Pro Definition Query that will just select the records if all the lithologies for a given well are AQTYPE=D. So when done it would select 70000000017, 70000000018, and 70000000019 because all AQTYPE=D. But it would not select 70000000020 or 70000000021 because they both have some AQTYPE=R. Thanks WELLIDSEQ_NUMPRIM_LITHDEPTHTHICKNESSAQTYPE700000000171.00000Clay42.0000042.00000D700000000172.00000Sand60.0000018.00000D700000000173.00000Sand & Clay94.0000034.00000D700000000174.00000Sand125.0000031.00000D700000000181.00000Clay50.0000050.00000D700000000182.00000Sand118.0000068.00000D700000000191.00000Sand30.0000030.00000D700000000192.00000Clay50.0000020.00000D700000000193.00000Gravel63.0000013.00000D700000000201.00000Clay60.0000060.00000D700000000202.00000Clay & Gravel90.0000030.00000D700000000203.00000Clay & Stones105.0000015.00000D700000000204.00000Sandstone130.0000025.00000R700000000211.00000Sand & Gravel20.0000020.00000D700000000212.00000Sand & Clay50.0000030.00000D700000000213.00000Clay & Stones72.0000022.00000D700000000214.00000Limestone92.0000020.00000R700000000215.00000Sandstone122.0000030.00000R Ottawa_lith_dbf_SUBSET.xlsx

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

ArcGIS Pro Definition Query to select a group of rows with all the same attribute

JohnEsch1

I work with a water well data with a related table of lithologies. So a given well can have 1 or many related lithologies. The WELLID is a unique for a given well. 70000000017 has 4 lithologies where 70000000018 has 2. Each lithology can be either glacial drift (AQTYPE=D) or bedrock (AQTYPE=R). Some wells never tagged bedrock so they have all AQTYPE=D. Others have generally the upper part as AQTYPE=D and the lower part as AQTYPE=R. I am trying to write an ArcGIS Pro Definition Query that will just select the records if all the lithologies for a given well are AQTYPE=D. So when done it would select 70000000017, 70000000018, and 70000000019 because all AQTYPE=D. But it would not select 70000000020 or 70000000021 because they both have some AQTYPE=R. Thanks

WELLID	SEQ_NUM	PRIM_LITH	DEPTH	THICKNESS	AQTYPE
70000000017	1.00000	Clay	42.00000	42.00000	D
70000000017	2.00000	Sand	60.00000	18.00000	D
70000000017	3.00000	Sand & Clay	94.00000	34.00000	D
70000000017	4.00000	Sand	125.00000	31.00000	D
70000000018	1.00000	Clay	50.00000	50.00000	D
70000000018	2.00000	Sand	118.00000	68.00000	D
70000000019	1.00000	Sand	30.00000	30.00000	D
70000000019	2.00000	Clay	50.00000	20.00000	D
70000000019	3.00000	Gravel	63.00000	13.00000	D
70000000020	1.00000	Clay	60.00000	60.00000	D
70000000020	2.00000	Clay & Gravel	90.00000	30.00000	D
70000000020	3.00000	Clay & Stones	105.00000	15.00000	D
70000000020	4.00000	Sandstone	130.00000	25.00000	R
70000000021	1.00000	Sand & Gravel	20.00000	20.00000	D
70000000021	2.00000	Sand & Clay	50.00000	30.00000	D
70000000021	3.00000	Clay & Stones	72.00000	22.00000	D
70000000021	4.00000	Limestone	92.00000	20.00000	R
70000000021	5.00000	Sandstone	122.00000	30.00000	R

Ottawa_lith_dbf_SUBSET.xlsx

Find more posts tagged with

Geodatabase

Desktop

Accepted answers

MapGrrl

I just saw your sample data. I had to import it into a GDB, since that's the only way the subquery will work, but then I use this syntax:

WELLID NOT IN (SELECT WELLID FROM Ottawa_lith_dbf_test WHERE AQTYPE='R')

All comments

JohnEsch2

MapGrrl, This has worked for me in the past, but sometimes when I try this on a table in a GDB, the table name turns light green

WELLID NOT IN (SELECT WELLID FROM CoCalhoun_lith_FINAL_TtE_xlsx_csvET WHERE AQTYPE='R')

When I select Apply I get a Error message: There was an error with the expression. Do you have any suggestions?

JohnEsch1

This worked great, thanks MapGrrl!

MapGrrl

I just saw your sample data. I had to import it into a GDB, since that's the only way the subquery will work, but then I use this syntax:

WELLID NOT IN (SELECT WELLID FROM Ottawa_lith_dbf_test WHERE AQTYPE='R')

MapGrrl

Are there only the two types, R and D? If so, use a query to find all the wells that HAVE AQTYPE=R, then use the subquery to ID the rest of the wells based on what's NOT IN the list of wells with AQTYPE=R. Again, syntax might be a bit off, but something like:

WHERE WELLID NOT IN (SELECT WELLID FROM TableName WHERE AQTYPE=R)

https://pro.arcgis.com/en/pro-app/latest/help/mapping/navigation/sql-reference-for-elements-used-in-query-expressions.htm#GUID-F5320DB5-4E54-4D49-BF31-A58F5379ED9F

MapGrrl

This may be more of a Data Schema issue than a query issue. My R-focused brain says "use dplyr to spread the data so that each row represents one well, and then have multiple columns for each variable associated with the Seq Num"... I'm not sure that's possible using just OOTB Pro without a Python Script (I'm sure there's a dplyr equivalent library out there for Py). This is a good question though!

MapGrrl

I'm probably not 100% on syntax, but something like:

WHERE WELLID IN (SELECT WELLID FROM TableName WHERE AQTYPE=D)

JohnEsch1

A given county may 10,000-30,000 water wells. WELL LIKE "7000000001%" AND AQTYPE="D" would just give me the ATYPE="D" lithologies, but this well may also have AQTYPE="R" I am trying to filter for just wells where all the lithologies for a given well are AQTYPE=D. Thanks

MapGrrl

Nevermind, I think I understand what's happening now. You need more of an If-Then Statement, but I'm not sure DQ supports. Let me investigate further.

MapGrrl

How many AQTYPE fields are in the data?

Why can't you use AQTYPE="D"?

Could you use WELLID LIKE "7000000001%"?

Or a compound query: WELL LIKE "7000000001%" AND AQTYPE="D"?

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.