Definition Query based off a 1:M relationship

[AlfredBaldenweck]

Hi all,

I have a feature class and a table (sites and monitoring records) in an enterprise GDB.

I'm trying to create a definition query to filter the feature class by the status of the most recent monitoring visit in the related table.

I'm kind of at a loss for how to do it. (Side note: this would be easier if the SQL window were improved Improve SQL Expression Builder - Esri Community)

I'm thinking that the best way would be to:

1. Group the monitoring data by site number,
2. Take the MAX date from the grouped monitoring data,
3. Join the filtered monitoring records to the feature class
4. Filtering based off the joined field where the Status is equal to [Status].

Unfortunately, I am pretty bad at SQL and have no idea where or how to do this.

I kind of have an expression for the subquery to filter by date:

• (SELECT MAX(Mon_Date) FROM Monitoring_Table GROUP BY Site_ID)

But nothing else, and I'm not even sure if this works. SQL reference for query expressions used in ArcGIS—ArcGIS Pro | Documentation mentions that joins are tricky in enterprise GDBs, which adds to another layer of confusion.

If anyone has any insight into this, I'd appreciate it.

Thanks!

[cashbay_Fishbeck]

Hi!  Were you able to figure this out?  I am trying to symbolize based on data in a table from a 1 to Many relationship.

[AlfredBaldenweck]

Yes! But not with a definition query. I used a query layer instead.

Huge thanks to @JohannesLindner for the original query. 

The following is for data in a SQL Server gdb; syntax may change depending on your system.

It also includes an added query to make sure you only get one record returned if, for example, your data isn't as clean as you'd like and there are duplicate records for the same day (which we found out the hard way).

SELECT 
t1.OBJECTID,
t1.Nest_ID,
t1.shape, 
t2.Nest_Status
/* other fields, etc*/ 

FROM db.owner.NestLocations_pt t1 
  INNER JOIN (
    SELECT MIN(OBJECTID) as OID, 
    Nest_ID, 
    MAX(NestMon_Date) as MaxDate   
    FROM db.owner.NestMonitoring_tb   
    GROUP BY Nest_ID ) MaxDates ON t1.Nest_ID = MaxDates.Nest_ID 
    INNER JOIN db.owner.NestMonitoring_tb t2 
	ON MaxDates.Nest_ID = t2.Nest_ID 
		AND MaxDates.MaxDate = t2.NestMon_Date 
		AND MaxDates.OID = t2.OBJECTID

The cool thing about the query layers is that you can still do definition queries, etc. on them after they come in, and you can save them as layer files. A disadvantage (at least in Pro 2.9) is that if you copy the layer between maps, it breaks the sources, so you have to re-add the query layer again.

The success of this has put us on to query layers for a whole bunch of things, so I highly recommend.

Alternatively (not really tried this yet since all of the data we'd do this with is in the eGDB) you should be able to use this query to make a view instead, especially if you want to work with file geodatabases. Just make sure to name it so you know it's a view by looking at it. (Also be prepared for different syntax if in a file geodatabase.)