ORACLE JOIN after LEFT JOIN-CodePudding

I have four tables, three of them joined with a LEFT JOIN to the main file.

SELECT ZADOCO, CADOCO, RDRORN, RDOORN, PDDOCO 
FROM PRODDTA.F1755 T2
        LEFT JOIN PRODDTA.F90CG503 TCSRMA ON TCSRMA.CAASTYP = 'R' AND TCSRMA.CADOCO = ZADOCO
        LEFT JOIN PRODDTA.F40051 TRMAOS ON (TCSRMA.CAKCOO = TRMAOS.RDRKCO AND TCSRMA.CARORN = TRMAOS.RDRORN AND TCSRMA.CADCTO = TRMAOS.RDRCTO)
            LEFT JOIN PRODDTA.F4311 ON (TRMAOS.RDOKCO = PDKCOO AND TO_NUMBER(TRIM(TRMAOS.RDOORN)) = PDDOCO AND TRMAOS.RDOCTO = PDDCTO AND TRMAOS.RDPOLN = PDLNID AND (PDLTTR, PDNXTR) NOT IN (('980', '999')))
            WHERE ZASTAW = '2' AND ZADOCO IN (172755, 1672095, 1675619)

This query might bring me duplicates, because they are all LEFT.

How can I restrict the results so table F40051 depends on F4311 (i.e. it doesn't show F40051 (RD) if F4311 (PD) doesn't exist), but still show value 1672095? If I apply an INNER JOIN to F4311...

SELECT ZADOCO, CADOCO, RDRORN, RDOORN, PDDOCO 
FROM PRODDTA.F1755 T2
        LEFT JOIN PRODDTA.F90CG503 TCSRMA ON TCSRMA.CAASTYP = 'R' AND TCSRMA.CADOCO = ZADOCO
        LEFT JOIN PRODDTA.F40051 TRMAOS ON (TCSRMA.CAKCOO = TRMAOS.RDRKCO AND TCSRMA.CARORN = TRMAOS.RDRORN AND TCSRMA.CADCTO = TRMAOS.RDRCTO)
            JOIN PRODDTA.F4311 ON (TRMAOS.RDOKCO = PDKCOO AND TO_NUMBER(TRIM(TRMAOS.RDOORN)) = PDDOCO AND TRMAOS.RDOCTO = PDDCTO AND TRMAOS.RDPOLN = PDLNID AND (PDLTTR, PDNXTR) NOT IN (('980', '999')))
            WHERE ZASTAW = '2' AND ZADOCO IN (172755, 1672095, 1675619)

... I will lose one record.

Adding a subquery might impact me with the performance, as there would be no direct link, but the SQL would need to run the entire SELECT in the subquery first, which will make it very slow.

Any ideas?

CodePudding user response：

I found by chance a solution:

CodePudding user response：

Perform the join in a sub-query:

SELECT ZADOCO,
       CADOCO,
       RDRORN,
       RDOORN,
       PDDOCO 
FROM   PRODDTA.F1755 T2
       LEFT JOIN PRODDTA.F90CG503 TCSRMA
       ON (   TCSRMA.CAASTYP = 'R'
          AND TCSRMA.CADOCO = ZADOCO)
       LEFT JOIN (
         SELECT * -- Name the columns you want.
         FROM   PRODDTA.F40051 TRMAOS
                JOIN PRODDTA.F4311
         ON (   TRMAOS.RDOKCO = PDKCOO
            AND TO_NUMBER(TRIM(TRMAOS.RDOORN)) = PDDOCO
            AND TRMAOS.RDOCTO = PDDCTO
            AND TRMAOS.RDPOLN = PDLNID
            AND (PDLTTR, PDNXTR) NOT IN (('980', '999')))
       )
       ON (   TCSRMA.CAKCOO = TRMAOS.RDRKCO
          AND TCSRMA.CARORN = TRMAOS.RDRORN
          AND TCSRMA.CADCTO = TRMAOS.RDRCTO)
WHERE  ZASTAW = '2'
AND    ZADOCO IN (172755, 1672095, 1675619)