topic Re: SQL query expression in related table in Data Management Questions

SQL query expression in related table

luxairport_rim — Wed, 26 May 2021 11:26:16 GMT

Good afternoon

I have a feature of points <Points_defauts> that has a related table, used to store the evolution of a certain object

I am trying to do a table join in ArcMap, in order to obtain the latest record.

I started to do a definition query in the related table in order to get the latest value collected, an then do the table join.

In my related table I have has an external Key field [Point_ID] and a field date [CreationDate].

I just want to have the latest record, based on the date field.

In made some google and I found this query:

[POINT_ID],[CREATIONDATE] IN

(SELECT [POINT_ID],[CREATIONDATE]

FROM TABLE AS table1

GROUP BY [POINT_ID]

HAVING COUNT([POINT_ID]) > 1)

However, an expression error is the only result I have.

Is this expression possible in ArcMap?

What I am doing wrong??

Sorry, but I’m newbie in sql expressions…

Re: SQL query expression in related table

JoshuaBixby — Wed, 26 May 2021 15:29:43 GMT

If you haven't done so already, I suggest you read About joining and relating tables—ArcMap | Documentation (arcgis.com).

You mention "my related table," have you set up a table relation in the software between the two tables, or are you just talking about two tables that are related logically but you haven't created a formal relation between them in ArcMap?

Re: SQL query expression in related table

luxairport_rim — Wed, 26 May 2021 19:47:07 GMT

I have done the relationship 1:N in the geodatabase correctly, with intern and external key.
my problem is after… I have several rows with same ID with different dates

i want make a query view in the related table that give me only the last register based on date

in AGOL it is possible, but I need to have the same function in ArcMap

Re: SQL query expression in related table

luxairport_rim — Wed, 02 Jun 2021 06:15:42 GMT

Help please.. can anyone help me?