How to keep field in Query layer - join

MarcoPoetsch_ret

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?

 

Capture.JPG

KevinDunlop

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:

<SPAN class="keyword token">SELECT</SPAN> PointLayer<SPAN class="punctuation token">.</SPAN><SPAN class="operator token">*</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="keyword token">Table</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="operator token">*</SPAN> 
<SPAN class="keyword token">FROM</SPAN> PointLayer
<SPAN class="keyword token">JOIN</SPAN> <SPAN class="keyword token">Table</SPAN> 
&nbsp; <SPAN class="keyword token">ON</SPAN> PointLayer<SPAN class="punctuation token">.</SPAN>Assess <SPAN class="operator token">=</SPAN> <SPAN class="keyword token">Table</SPAN><SPAN class="punctuation token">.</SPAN>Field2A‍‍‍‍‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

This is for only certain fields:

<SPAN class="keyword token">SELECT</SPAN> PointLayer<SPAN class="punctuation token">.</SPAN>Field1A<SPAN class="punctuation token">,</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; PointLayer<SPAN class="punctuation token">.</SPAN>Shape<SPAN class="punctuation token">,</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; PointLayer<SPAN class="punctuation token">.</SPAN>OBJECTID<SPAN class="punctuation token">,</SPAN> 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">Table</SPAN><SPAN class="punctuation token">.</SPAN>Field2B<SPAN class="punctuation token">,</SPAN> 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">Table</SPAN><SPAN class="punctuation token">.</SPAN>Field2C<SPAN class="punctuation token">,</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">Table</SPAN><SPAN class="punctuation token">.</SPAN>Field2D
<SPAN class="keyword token">FROM</SPAN> PointLayer
<SPAN class="keyword token">JOIN</SPAN> <SPAN class="keyword token">Table</SPAN> 
&nbsp; <SPAN class="keyword token">ON</SPAN> PointLayer<SPAN class="punctuation token">.</SPAN>Assess <SPAN class="operator token">=</SPAN> <SPAN class="keyword token">Table</SPAN><SPAN class="punctuation token">.</SPAN>Field2A‍‍‍‍‍‍‍‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>