Oracle SQL Join Remove and Duplicate Rows

Question

I have three tables in Oracle DB,

01. TBL_ITS_INS_CLAIM_TRANSACTIONS

╔═════════════════════════════╗
║ Id  PolicyMasterID  Ref_No  ║
╠═════════════════════════════╣
║ 01    106              P100 ║
║ 02    106              P200 ║
╚═════════════════════════════╝





02. TBL_ITS_INS_CLAIM_SETTLEMENT

╔══════════════════════════════╗
║ Id   CLAIM_TRANSACTIONS_ID   ║
╠══════════════════════════════╣
║ 01    01                     ║
║ 02    01                     ║
║ 03    01                     ║
╚══════════════════════════════╝


03. TBL_ITS_INS_CLAIM_SUBROGATION

╔════════════════════════════╗
║ Id   CLAIM_TRANSACTIONS_ID ║
╠════════════════════════════╣
║ 01    01                   ║
║ 02    01                   ║
║ 03    01                   ║
╚════════════════════════════

what i have tried is , i wrote below query for get Results...but its duplicates rows. i have edited code :

SELECT TBL_ITS_INS_CLAIM_TRANSACTIONS.ID as "Claim Trans ID",      
  TBL_ITS_INS_CLAIM_SETTLEMENT.ID as "SettlementId",
  TBL_ITS_INS_CLAIM_SUBROGATION.ID as "SUBROGATION ID"
FROM TBL_ITS_INS_CLAIM_TRANSACTIONS
LEFT JOIN TBL_ITS_INS_CLAIM_SUBROGATION
ON TBL_ITS_INS_CLAIM_TRANSACTIONS.ID = TBL_ITS_INS_CLAIM_SUBROGATION.CLAIM_TRANSACTIONS_ID
LEFT JOIN TBL_ITS_INS_CLAIM_SETTLEMENT
ON TBL_ITS_INS_CLAIM_TRANSACTIONS.ID = TBL_ITS_INS_CLAIM_SETTLEMENT.CLAIM_TRANSACTIONS_ID

Result in above query

╔══════════════════════════════════════════════════╗
║ Claim_Trans_ID    SettlementId   SUBROGATION _ID ║
╠══════════════════════════════════════════════════╣
║ 106                   01              01         ║
║ 106                   01              02         ║
║ 106                   02              01         ║
║ 106                   02              02         ║
║ 106                   03              01         ║
║ 106                   03              02         ║
╚══════════════════════════════════════════════════╝

i want get like below result

╔══════════════════════════════════════════════════╗
║ Claim_Trans_ID    SettlementId   SUBROGATION _ID ║
╠══════════════════════════════════════════════════╣
║ 106                     01            01         ║
║ 106                     02            02         ║
║ 106                     03                       ║
╚══════════════════════════════════════════════════╝

how can i get result like above

Answer 1

Just a comment, but too long.

I appreciate you effort in formatting data, but this is not so useful for people to reproduce your case and test some code.

The best way (IMHO) to post sample data is posting something that people can easily run to create the case, with no need for editing.

In your question I would post sample data as:

create table TBL_ITS_INS_CLAIM_TRANSACTIONS(Id, PolicyMasterID, Ref_No) as (
  select '01', 106, 'P100' from dual union all
  select '02', 106, 'P100' from dual
);
create table TBL_ITS_INS_CLAIM_SETTLEMENT(ID, CLAIM_TRANSACTIONS_ID) as (
  select '01', '01' from dual union all
  select '02', '01' from dual union all
  select '03', '01' from dual
);
create table TBL_ITS_INS_CLAIM_SUBROGATION (Id, CLAIM_TRANSACTIONS_ID) as (
select '01', '01' from dual union all
select '02', '01' from dual union all
select '03', '01' from dual
);

Once created the tables, it's easy to notice that your table structure doesn't match your query:

SQL> SELECT TBL_ITS_INS_CLAIM_TRANSACTIONS.ID as "Claim Trans ID",
  2    TBL_ITS_INS_CLAIM_SETTLEMENT.ID as "SettlementId",
  3    TBL_ITS_INS_CLAIM_SUBROGATION.ID as "SUBROGATION ID"
  4  FROM TBL_ITS_INS_CLAIM_TRANSACTIONS
  5  LEFT JOIN TBL_ITS_INS_CLAIM_SUBROGATION
  6  ON TBL_ITS_INS_CLAIM_TRANSACTIONS.ID = TBL_ITS_INS_CLAIM_SUBROGATION.CLAIM_TRAN_SUM_ID
  7  LEFT JOIN TBL_ITS_INS_CLAIM_SETTLEMENT
  8  ON TBL_ITS_INS_CLAIM_TRANSACTIONS.ID = TBL_ITS_INS_CLAIM_SETTLEMENT.CLAIM_TRAN_SUM_ID;
ON TBL_ITS_INS_CLAIM_TRANSACTIONS.ID = TBL_ITS_INS_CLAIM_SUBROGATION.CLAIM_TRAN_SUM_ID
                                       *
ERROR at line 6:
ORA-00904: "TBL_ITS_INS_CLAIM_SUBROGATION"."CLAIM_TRAN_SUM_ID": invalid
identifier