Left Join in MakeQueryLayer

193
2
10-29-2021 03:13 PM
AllenScully2
New Contributor II

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'
0 Kudos
2 Replies
DonMorrison1
Occasional Contributor III

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?

0 Kudos
AllenScully2
New Contributor II
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.
0 Kudos