I need to join one feature class and one table. This is a one to multiple join, therefore, my only option seems to be by creating a query table. My problem is that the fields I want to join do not seem to be matched properly when I run the query table tool. I've tried using the following expression in the query table expression field to indicate that these are the fields to be joined:
("Infraestructura.idInfraestructura" = "InfraestructuraServicio.idInfraestructura") AND ("InfraestructuraServicio.idServicio" = "Servicio.idServicio")
But the layer result is empty.
I think I may be doing something wrong (perhaps the problem is with my query), but I'm not sure what. When I verify my query in the query builder window, I keep getting a message saying
The expression was verified successfully but no records were returned
which does not make any sense as there should definitely be some matches.
See these posts no bug number but it appears the bug has been around awhile
Poked around a bit, it turns out a related issue has been around since ArcGIS 9.2!
Make Query Table Tool SQL Query Validation throws an error on valid query expressions when the expression contains a statement to set field in one table equal to a field in a second table.
Supposedly it is "In Product Plan," they are just moving rrreeeaaallllyyyy ssslllooowwwlllyyy. I guess since ignoring the error works, they figure they have time.
Funny enough, a very similar bug was logged with ArcGIS 10.0!
The SQL query validation in the Make Query Table tool displays an error on valid query expressions.
Oddly, this bug is still "New," which I guess makes some sense given they are still working on fixing the same bug in 9.2.
The problem, or a similar one, was logged again at ArcGIS 10.2.2. The response is such a doozy I have to include it all.
The Make Query Table tool does not correctly process data from file geodatabases.
This works correctly in the current development build. Note that the quotes around the field names in the join expression must be removed for this to work. So "FeatureClass.FeatureID" = "Table.TableID" must be FeatureClass.FeatureID = Table.TableID
1. Migrate the data to a personal geodatabase. 2. Make sure to adjust the syntax of the expression to brackets.
The status is "Not in Current Product Plan," which is a bit confusing to me, but maybe they don't plan on addressing it because it is already working in the "current development build," although we have no idea what build that is. My favorite suggestion is to copy data to a personal geodatabase, which we know the expression builder doesn't build valid expressions for. The user could put the brackets in themselves, just don't try to verify the syntax because you will get a bogus error message.
Because the issue still exists, and seems to be larger than just validating syntax, I opened another bug with Esri. Who knows, maybe fourth time will be a charm.
The Query expression builder in the Make Query Table geoprocessing tool uses inconsistent delimiter syntax, throws inconsistent errors and leads to inconsistent results in 10.4.1
At the end I did it!
In Key Field Options I selected USE_KEY_FIELDS, but then I don't indicated any of them in the table below. It seems that ArcGIS is taken by default or through the expression.
In this way, the tool has worked ......
Thanks to everyone!