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

[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"?

[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]

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]

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]

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-...

[MapGrrl · Accepted solution]

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')

[JohnEsch1]

This worked great, thanks MapGrrl!

[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?