Joining multiple tables in PostgreSQL using ExecuteSQL processor

[#1]

Dear All,

Currently I am using below SQL query in Execute SQL processor (SQL select query) to join 3 different tables that exist within my PostgreSQL database.

SELECT T1.*,T2.*,T3.* 
FROM  amsterdam.amd_block_info T1 
LEFT JOIN amsterdam.amd_ap_info T2 ON T2.ap_track_id = T1.track_id
LEFT JOIN amsterdam.amd_track_info T3 ON T2.ap_track_id = T3.track_id;

But I am getting below error response.

ExecuteSQL[id=3c9f01f3-0176-1000-0000-000032d6da19] Unable to execute SQL select query SELECT T1.*,T2.*,T3.* 
FROM  amsterdam.amd_block_info T1 
LEFT JOIN amsterdam.amd_ap_info T2 ON T2.ap_track_id = T1.track_id
LEFT JOIN amsterdam.amd_track_info T3 ON T2.ap_track_id = T3.track_id; due to org.apache.avro.AvroRuntimeException: Duplicate field track_id in record any.data.amd_block_info: track_id type:UNION pos:11 and track_id type:UNION pos:3.. No FlowFile to route to failure: org.apache.nifi.processor.exception.ProcessException: org.apache.avro.AvroRuntimeException: Duplicate field track_id in record any.data.amd_block_info: track_id type:UNION pos:11 and track_id type:UNION pos:3.

Am I making any mistake in the query?
So,can anyone please tell me what kind of error is this and how can I get rid of this error?

[#2]

@DarkStar  Is the join query works fine on PostgreSQL CLI?

If yes, then what is the jdbc driver you are using? is that jdbc version supports joins with duplicate fields? It looks like you have T1.track_id and T3.track_id which are conflicting. May be can you try to change the field name, test it on psql CLI and then test again in NiFi ?

-Akash

[#3 · Accepted solution]

Hello Akash, 

Thanks for your feedback. Actually, its work fine if I change my query as below:

SELECT
DISTINCT T1.block_id AS block_id,
T1.block_name AS block_name,
T1.block_kp_begin AS block_kp_begin,
T1.block_kp_end AS block_kp_end,
T2.ap_track_id AS ap_track_id,
T2.ap_kp_in_track AS ap_kp_in_track,
T2.ap_physical_name AS ap_physical_name,
T3.track_name AS track_name,
T3.track_type AS track_type 
FROM  amsterdam.amd_block_info T1 
LEFT JOIN amsterdam.amd_ap_info T2 ON T1.track_id = T2.ap_track_id
LEFT JOIN amsterdam.amd_track_info T3 ON T1.track_id = T3.track_id;

Here all I needed to define my variable as Alias again then the problem fixed.