I have to join three tables for my report.

Table1:

DTL_MTCH_KEY  TRAN_AM   SYS_IND    
  1234567       10        
  1234567       10
  1234567       10
  1234567       10
  2222222       25         Y
  2222222       25         Y

Table2:

DTL_MTCH_KEY    SUM_TRAN_AM     JR_NAME
   1234567          40          AAAAAAA
   2222222          50          BBBBBBB

Table3:

DTL_MTCH_KEY   SYS_IND    ERR_MSG 
   2222222        Y       ISSUE WITH PRODUCT 

My output must be like below

DTL_MTCH_KEY  TRAN_AM   JR_NAME  ERR_MSG 
  1234567       10      AAAAAAA             
  1234567       10      AAAAAAA
  1234567       10      AAAAAAA
  1234567       10      AAAAAAA
  2222222       25      BBBBBBB  ISSUE WITH PRODUCT       
  2222222       25      BBBBBBB  ISSUE WITH PRODUCT 

Requirement.

1. I want to join table1, 2 and 3.
2. List all the values from Table 1 and get the JR_NAME from table2.
3. Whenever Table1 SYS_IND is Y, then get the ERR_MSG from Table3.
4. I have to do this in single query.

I tried below and it bring up duplicate rows. I had 229002 rows in my TABLE1 but my query bring up 385717 rows.

Select A.DTL_MTCH_KEY, A.TRAN_AM, B.JR_NAME, C.ERR_MSG 
FROM TABLE1 A 
     LEFT JOIN TABLE2 B 
        ON (A.DTL_MTCH_KEY = B.DTL_MTCH_KEY) 
     LEFT JOIN TABLE3 C 
        ON (A.DTL_MTCH_KEY = C.DTL_MTCH_KEY 
            AND C.SYS_IND = 'Y') 
WHERE DATE = '2022-05-26'

Thanks in Advance

CodePudding user response：

select A.DTL_MTCH_KEY,A.TRAN_AM,B.JR_NAME,C.ERR_MSG from Table1 A INNER JOIN Table2 B on A.DTL_MTCH_KEY = B.DTL_MTCH_KEY Left JOIN Table3 C on A.DTL_MTCH_KEY = C.DTL_MTCH_KEY AND A.SYS_IND = C.SYS_IND AND A.SYS_IND ='Y'

CodePudding user response：

try using the following queries