Make query table error

[AlonsoDiego]

Hi,

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.

Thanks

[JoshuaBixby]

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?

[AlonsoDiego]

Hi Joshua,

     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"

​Thanks!

[JoshuaBixby]

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?

[AlonsoDiego]

Hi Joshua,

           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...

Thanks!

[JoshuaBixby]

It is good you uploaded data, there are several important aspects that weren't described originally.

• The data is in a personal geodatabase
  • Although most tools still support working with personal geodatabases, it is always good to disclose what type of back-end storage is being used, especially shape files and personal geodatabases.  As versions march on, the number of quirks with shape files and personal geodatabases grow.
• InfraestructuraServicio is a relationship class
  • Although the Make Query Table documentation doesn't explicitly state that relationship classes are not supported, it also doesn't say they are supported.  All of the language in the documentation talks about "tables."
• Infraestructura is in a feature dataset while the other object/tables are in the root-level of the geodatabase.
  • According to the Make Query Table documentation, "All input feature classes or tables must be from the same input workspace."  I don't have a link to the documentation on-hand, but I believe feature datasets are considered workspaces in and of themselves.
• You are using field aliases that differ from actual field names and they have spaces in them.
  • I don't know whether this is affecting anything, but it is something I typically don't work with, especially field aliases with spaces in the name.

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

[WesMiller]

At 10.2 Make query table has a bug. Not sure what version your working with, but try removing the quotes from your field names.

[AlonsoDiego]

Hi Wes,

    I'm working with 10.3 version of ArcGIS.... I try removing the quotes but the table don´t have any records too...

Thanks!

[WesMiller]

I got it to work in 10.2 this way.

Infraestructura.idInfraestructura = InfraestructuraServicio.idInfraestructura AND Servicio.idServicio = InfraestructuraServicio.idServicio

[JoshuaBixby]

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.