desktop definition query subquery
Hello, I have a feature class called SupplyWells in a relationship class with a table called SamplingEvents. For a specified Well Type, I want to find wells where there was no detections of contaminants in the first year of sampling and detections after that year. Two fields in SamplingEvents that should answer this are Sampling_Detects_Flag and Days. Sampling_Detects_Flag is either NULL for no detection or YES for detection. Days is the number of days from the date of the first sampling event to the date of the sampling event of that record.
I've tried this: WELL_TYPE = 'WELL POINT' and EGAD_SEQ in (Select SAMPLE_POINT_SEQ from Sampling_Events where SAMPLE_DETECTION_FLAG is NULL and DAYS <= 365) and EGAD_SEQ in (Select SAMPLE_POINT_SEQ from Sampling_Events where SAMPLE_DETECTION_FLAG = 'YES' and DAYS > 365). However, it returns wells with at least one non-detect during the first 365 days. I want no detections during the first 365 days.
The attached image shows for the WELL POINT meets the 2 criteria: there were no detections <= 365 days and there were detections > 365 days (at Day 1704).
Your assistance is appreciated.
