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:
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:
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!
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.
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.)