How to keep field in Query layer - join

660
2
Jump to solution
07-19-2018 09:48 PM
MarcoPoetsch_ret
New Contributor III

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 1AField 1BField 1CField 1Dbased onField 2AField 2BField 2CField 2D
Unique ID 1111111Field AUnique ID 1AAAAAA
Unique ID 2222222Unique ID 2BBBBBB
Unique ID 3333333Unique ID 3CCCCCC
Unique ID 4444444Unique ID 4DDDDDD
RESULT what I getResult what I want to have
Field 1A/1BShapeOBJECTIDESRI_OIDField 1A/1BShapeOBJECTIDESRI_OIDField 2BField 2CField 2D
Unique ID 1Point1301210Unique ID 1Point1301210AAAAAA
Unique ID 2Point81639Unique ID 2Point81639BBBBBB
Unique ID 3Point44326Unique ID 3Point44326CCCCCC
Unique ID 4Point60061Unique ID 4Point60061DDDDDD

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?

 

0 Kudos
1 Solution

Accepted Solutions
KevinDunlop
Occasional Contributor III

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

View solution in original post

2 Replies
KevinDunlop
Occasional Contributor III

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

MarcoPoetsch_ret
New Contributor III

Bingo!
Thanks a lot!

0 Kudos