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?

[KevinDunlop · Accepted solution]

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]

Bingo!
Thanks a lot!