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.
If you would "Copy As Python Snippet" and paste the resulting ArcPy code here, it might help people reply since they would have more of the arguments.
Have you tried using just one or the other condition? Do you get results from doing both alone?
Here I post the code:
... # Import arcpy module
... import arcpy
... # Local variables:
... Infraestructura = "Infraestructura"
... InfraestructuraServicio = "InfraestructuraServicio"
... Servicio = "Servicio"
... query2 = "query2"
... # Process: Make Query Table
... arcpy.MakeQueryTable_management("Infraestructura;InfraestructuraServicio;Servicio", query2, "ADD_VIRTUAL_KEY_FIELD", "", "Infraestructura.Shape #;Infraestructura.idInfraestructura #;Servicio.idServicio #", "Infraestructura.idInfraestructura = InfraestructuraServicio.idInfraestructura AND Servicio.idServicio = InfraestructuraServicio.idServicio"
I see what you are trying to do, and it generally seems right. Could you sanitize some subsets of data and post them?
Getting back to the other question I asked earlier. What happens if you put only one condition in at a time? Results, no results?
I uploaded my geodatabase to dropbox, here is the link: https://dl.dropboxusercontent.com/u/108321176/Infraestructura.mdb
When I put only one condition the results are wrong...
I hope someone can help me with this...
It is good you uploaded data, there are several important aspects that weren't described originally.
I tried a handful of changes here and there, but I kept generating different error messages. I don't know what factors above are playing into the issue you are having. I would start by creating a file geodatabase and working with 3 tables (no relationship classes) with no field aliases in the root-level of the geodatabase. If that works, start adding back in aspects like field aliases or move data to personal geodatabase. At some point, you will stumble on what is breaking.
UPDATE: It appears the tool will work with the data as-is, if the quotes are removed from the expression. Furthermore, there seems to be a bug in the expression builder that generates an error when the quotes are removed and the expression is tested, but yet the expression still works
I got it to work in 10.2 this way.
Infraestructura.idInfraestructura = InfraestructuraServicio.idInfraestructura AND Servicio.idServicio = InfraestructuraServicio.idServicio
Interesting. I am running 10.4.1 and got your syntax to work. Why I didn't think it would work when I tried earlier was that I was using the expression builder to validate expressions and it generates an error: "There was an error with the expression. General function failure [Infraestructura] Too few parameters. Expected 3."
So, it seems to work, but there is a bug in the expression builder that says the expression isn't valid when tested.