topic Re: How to keep field in Query layer - join in Geodatabase Questions

How to keep field in Query layer - join

MarcoPoetsch_ret — Fri, 20 Jul 2018 04:48:59 GMT

Hi all,

I have the problem that, when I join two tables in a query layer I loose the fields I'm interested in.

I want to keep all fields (or at least some selected fields).

See attachment for more details:


Point Layer				<< Join >>	Table
Field 1A	Field 1B	Field 1C	Field 1D	based on	Field 2A	Field 2B	Field 2C	Field 2D
Unique ID 1	1	11	111	Field A	Unique ID 1	A	AA	AAA
Unique ID 2	2	22	222		Unique ID 2	B	BB	BBB
Unique ID 3	3	33	333		Unique ID 3	C	CC	CCC
Unique ID 4	4	44	444		Unique ID 4	D	DD	DDD


RESULT what I get					Result what I want to have
Field 1A/1B	Shape	OBJECTID	ESRI_OID		Field 1A/1B	Shape	OBJECTID	ESRI_OID	Field 2B	Field 2C	Field 2D
Unique ID 1	Point	13012	10		Unique ID 1	Point	13012	10	A	AA	AAA
Unique ID 2	Point	8163	9		Unique ID 2	Point	8163	9	B	BB	BBB
Unique ID 3	Point	4432	6		Unique ID 3	Point	4432	6	C	CC	CCC
Unique ID 4	Point	6006	1		Unique ID 4	Point	6006	1	D	DD	DDD

That's the code I have so far:

SELECT PointLayer.Field1A,PointLayer.Shape,PointLayer.OBJECTID from PointLayer

JOIN Table ON PointLayer.Assess=Table .Field2A

Any idea how to resolve my problem?

Re: How to keep field in Query layer - join

KevinDunlop — Sun, 12 Dec 2021 08:39:20 GMT

The problem is that you need to list the fields you want in both tables, not just the point FC.

This is for all fields in both PointLayer and table:

SELECT PointLayer.*, Table.* 
FROM PointLayer
JOIN Table 
  ON PointLayer.Assess = Table.Field2A‍‍‍‍‍‍‍‍‍‍‍

This is for only certain fields:

SELECT PointLayer.Field1A,
       PointLayer.Shape,
       PointLayer.OBJECTID, 
       Table.Field2B, 
       Table.Field2C,
       Table.Field2D
FROM PointLayer
JOIN Table 
  ON PointLayer.Assess = Table.Field2A‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: How to keep field in Query layer - join

MarcoPoetsch_ret — Mon, 23 Jul 2018 00:09:06 GMT

Bingo!
Thanks a lot!