title: Oracle complex Join
body: <p>I have three queries,query 1 and query 2 have to be inner joined on Customer_number
  Resultant output from query1 and query2 will be further inner joined on 3 keys customer_number,account_number,bank_number.</p>
  
  <p>Can anyone help me to write the queries for these.I am not able to derive it.</p>
  
  <p>Thanks in advance,</p>
  
  <p>Bhaskar</p>
  
  <p>Query1 :</p>
  
  <pre><code>SELECT
  CUST.BANK_NUMBER as CUSTBANKNUMBER,
  CUST.CUSTOMER_NUMBER,
  CUST.DATE_DECEASE,
  FROM
  SIGN_LND.T_CUSTOMER_MASTER_FILE_LND CUST
  LEFT OUTER JOIN
  SIGN_LND.T_CUST_USER_DEF_FIELDS_LND USER_DEF
  ON (CUST.CUSTOMER_NUMBER = USER_DEF.CUSTOMER_NUMBER AND CUST.EFF_DT = USER_DEF.EFF_DT)
  WHERE
  CUST.EFF_DT = TO_DATE('2015-01-22','YYYY-MM-DD')
  </code></pre>
  
  <p>Query 2 :</p>
  
  <pre><code>SELECT
  S.CUSTOMER_NUMBER,
  S.ALIAS_ACCOUNT_NBR,
  M.BANK_NUMBER,
  M.ACCOUNT_NUMBER,
  M.PRODUCT_CODE,
  M.APPLICATION_NUMBER,
  M.APPL_ID
  FROM
  (
  SELECT T1.BANK_NUMBER,
     T1.ACCOUNT_NUMBER,
     T1.PRODUCT_CODE,
     CAST (NULL AS VARCHAR(5))              AS SUB_PRODUCT_CODE,
     T1.APPLICATION_NUMBER,
     T1.APPL_ID
     FROM   SIGN_LND.T_LOAN_MASTER_FILE_LND T1
     INNER JOIN SIGN_LND.T_LOAN_PRODUCT_DEFINITION_LND T2
                ON  T1.BANK_NUMBER = T2.BANK_NUMBER
                AND T1.PRODUCT_CODE = T2.PRODUCT_CODE
                        AND T1.EFF_DT=TO_DATE('2015-01-22','YYYY-MM-DD')
                AND T2.EFF_DT=TO_DATE('2015-01-22','YYYY-MM-DD')
     WHERE          T1.PRODUCT_CODE IS NOT NULL
                AND T1.DATE_OPEN IS NOT NULL
     UNION ALL
     SELECT T1.BANK_NUMBER,
     T1.ACCOUNT_NUMBER,
     T1.PRODUCT_CODE,
     T1.PRODUCT_CODE                   AS SUB_PRODUCT_CODE,
     T1.APPLICATION_NUMBER,
     T1.APPL_ID
  
     FROM   SIGN_LND.T_TA_MASTER_FILE_LND T1
     INNER JOIN SIGN_LND.T_TA_PRODUCT_DEFINITION_LND T2
                ON  T1.BANK_NUMBER = T2.BANK_NUMBER
                AND T1.PRODUCT_CODE = T2.PRODUCT_CODE
                        AND T1.ACCOUNT_TYPE = T2.ACCOUNT_TYPE
                        AND T1.EFF_DT=TO_DATE('2015-01-22','YYYY-MM-DD')
                AND T2.EFF_DT=TO_DATE('2015-01-22','YYYY-MM-DD')
     LEFT JOIN SIGN_LND.T_BONUS_INT_MASTER_FILE_LND T3
               ON  T1.ACCOUNT_NUMBER = T3.ACCOUNT_NUMBER
               AND T1.BANK_NUMBER = T3.BANK_NUMBER
                       AND T3.EFF_DT=TO_DATE('2015-01-22','YYYY-MM-DD')
     WHERE         T1.PRODUCT_CODE IS NOT NULL
               AND T1.ACCOUNT_STATUS_CODE IS NOT NULL
               AND T1.DATE_OPEN IS NOT NULL
     UNION ALL
     SELECT T1.BANK_NUMBER,
     T1.ACCOUNT_NUMBER,
     T1.PRODUCT_CODE,
     T1.PRODUCT_CODE                     AS SUB_PRODUCT_CODE,
     T1.APPLICATION_NUMBER,
     T1.APPL_ID,
  
     FROM              SIGN_LND.T_TIME_MASTER_FILE_LND T1
     INNER JOIN SIGN_LND.T_TM_PRODUCT_DEFINITION_LND T2
                ON  T1.BANK_NUMBER = T2.BANK_NUMBER
                AND T1.PRODUCT_CODE = T2.PRODUCT_CODE
                        AND T1.EFF_DT=TO_DATE('2015-01-22','YYYY-MM-DD')
                AND T2.EFF_DT=TO_DATE('2015-01-22','YYYY-MM-DD')
     LEFT JOIN SIGN_LND.T_BONUS_INT_MASTER_FILE_LND T3
               ON  T1.ACCOUNT_NUMBER = T3.ACCOUNT_NUMBER
               AND T1.BANK_NUMBER = T3.BANK_NUMBER
                       AND T3.EFF_DT=TO_DATE('2015-01-22','YYYY-MM-DD')
     WHERE         T1.PRODUCT_CODE IS NOT NULL
               AND T1.ACCOUNT_STATUS_CODE IS NOT NULL
               AND T1.DATE_OPEN IS NOT NULL
                         )M
     INNER JOIN
     ( 
     SELECT
      T1.ACCOUNT_NUMBER,
      T1.CUSTOMER_NUMBER,
      T1.RELATIONSHIP ,
      T2.ALIAS_ACCOUNT_NBR,
      T2.APPLICATION_NUMBER
      FROM SIGN_LND.T_CUSTOMER_CROSS_REF_LND T1
      INNER JOIN
       SIGN_LND.T_ALIAS_ACCT_NBR_CRSS_REF_LND T2
       ON  T1.ACCOUNT_NUMBER=T2.ACCOUNT_NUMBER
        WHERE   ALIAS_ACCOUNT_NBR_TYPE='UK'
      GROUP BY  T1.ACCOUNT_NUMBER,
       T1.CUSTOMER_NUMBER,
        T1.RELATIONSHIP ,
       T2.ALIAS_ACCOUNT_NBR,
      T2.APPLICATION_NUMBER
      )S
      ON  M.ACCOUNT_NUMBER=S.ACCOUNT_NUMBER
      AND
       M.APPLICATION_NUMBER=S.APPLICATION_NUMBER
        )
  </code></pre>
  
  <p>Query3:</p>
  
  <pre><code>    SELECT
      SPLIT.BANK_NUMBER,
      SPLIT.CUSTOMER_NUMBER,
      SPLIT.DATE_MOVING_IN,
      SPLIT.TITLE,
      SPLIT.FIRST_NAME,
      SPLIT.SECOND_NAME,
      SPLIT.SURNAME,
      SPLIT.CURR_APT_NUMBER_UPS,
      SPLIT.CURR_HOUSE_NAME_UPS,
      SPLIT.CURR_HOUSE_NBR,
      SPLIT.CURR_STREET_UPS,
      SPLIT.CURR_DISTRICT_UPS,
      SPLIT.CURR_POST_TOWN_UPS,
      SPLIT.POSTAL_CODE,
      SPLIT.CURR_COUNTRY,
      CUST_EXT.LOYALTY_CARD_NBR,
      XREF.ACCOUNT_NUMBER,
      XREF.RELATIONSHIP as RELATIONSHIP_RB,
       RB_ALIAS_XREF.APPL_ID as RBXREFAPPID,RB_ALIAS_XREF.ALIAS_ACCOUNT_NBR AS ALIAS_ACCOUNT_NBR_RB,
       PROD_OFFER.DATE_CLOSE
     FROM
       (
         SIGN_LND.T_CIF_SPLIT_NAME_ADDR_UPS_LND SPLIT
         LEFT OUTER JOIN
         SIGN_LND.T_CUST_MASTER_EXTENSION_LND CUST_EXT
            ON (SPLIT.CUSTOMER_NUMBER = CUST_EXT.CUSTOMER_NUMBER AND               SPLIT.EFF_DT = CUST_EXT.EFF_DT)
            )
           LEFT OUTER JOIN
          (
           SIGN_LND.T_CUSTOMER_CROSS_REF_LND XREF
           LEFT OUTER JOIN
          (
           SELECT * FROM SIGN_LND.T_ALIAS_ACCT_NBR_CRSS_REF_LND WHERE  EFF_DT = TO_DATE('2015-01-22','YYYY-MM-DD') AND ALIAS_ACCOUNT_NBR_TYPE = 'RB'
          ) RB_ALIAS_XREF
          ON (XREF.ACCOUNT_NUMBER = RB_ALIAS_XREF.ACCOUNT_NUMBER AND  XREF.EFF_DT = RB_ALIAS_XREF.EFF_DT)
          LEFT OUTER JOIN
          SIGN_LND.T_PRODUCT_OFFER_PURCHASE_LND PROD_OFFER
          ON (XREF.BANK_NUMBER = PROD_OFFER.BANK_NUMBER AND XREF.ACCOUNT_NUMBER = PROD_OFFER.ACCOUNT_NUMBER AND XREF.EFF_DT = PROD_OFFER.EFF_DT)
            )
           ON (SPLIT.CUSTOMER_NUMBER = XREF.CUSTOMER_NUMBER AND SPLIT.EFF_DT = XREF.EFF_DT)
           WHERE SPLIT.EFF_DT = TO_DATE('2015-01-22','YYYY-MM-DD')``
  </code></pre>