topic Left Join in MakeQueryLayer in Python Questions

Left Join in MakeQueryLayer

AllenScully2 — Fri, 29 Oct 2021 22:13:13 GMT

Working in Jupyter Notebooks, arcpy -

Have a MakeQueryLayer function working, but it seems to be ignoring the 'Left' part of the join. It runs and creates an output, but is doing a 1-to-1/Inner join, and I need it to return ALL from one feature class, regardless of whether there is a match in the second table.

I have tried:

"Left Join" - no error just a 1-to-1 output.

"Left Outer Join" - same thing

"Outer Join" returns an unknown error and does not run

Abridged code:

select p.OBJECTID, p.SHAPE, w.field1...,

from FC1 as p

Left Join TABLE 2 as w

ON p.KEY=w.KEY

WHERE w.WO_MAINTTYPE='APM'

Re: Left Join in MakeQueryLayer

DonMorrison1 — Sun, 31 Oct 2021 16:56:23 GMT

It sounds like a database issue to me - what is the underlying database? Our systems use MS SQL Server so I usually test the select statements in the SQL Server Management Studio before using then in arcpy functions such as MakeQueryLayer. Can you so something similar to this?

Re: Left Join in MakeQueryLayer

AllenScully2 — Sun, 31 Oct 2021 22:05:58 GMT

Thanks -
Yeah the query works as designed with the same syntax in a sql server management studio.

I know make query layer used to not support anything other than a 1-to-1 join, so just looking to see if that has changed and if so what’s a the syntax needed by python for the arcpy function for a left join.