Как соединить таблицы?

Есть такой запрос который выбирает по условию where 3512 записей. В столбцах CLAIM.M_TAX_ORG_FRMD_ID = 987 и CLAIM.M_TAX_ORG_ADM_INFO_SRC_ID=983 <---- То есть все 3512 записей имеют такие значения

Первый запрос

SELECT
        CLAIM.R_DECL_DEB_REFUND_ID,
        CLAIM.NUM_DECLARATION,
        CLAIM.DATE_DECL,
        claim.statement_type,
        claim.source_system,
        TAX_ORG_SRC.CODE_NK,
        TAX_ORG_SRC.CODE_TPK,
        tp_info.iin_bin,
        tp_info.rnn,
        TP_INFO.NAME_R,
        TP_INFO.NAME_K,
        TP_INFO.NAME_E,
        TP_INFO.TYPE_TP,
        KBK_SRC.KBK_CODE,
        nvl(bank.bik, bank.head_bik),
        claim.bank_account_ret,
        kbk_dst.kbk_code,
        operation_type_dst.name_r,
        operation_type_dst.name_k,
        TAX_ORG_ADM_INFO_DST.iin_bin,
        TAX_ORG_ADM_INFO_DST.RNN,
        CLAIM.AMOUNT,
        assign_payment.payment_code,
        claim.status_decl,
        claim.date_process,
        CLAIM.ERROR_DESC,
        CLAIM.DECLINE_CODE,
         court.judge,
         court.doc_num,
         CLAIM.M_TAX_ORG_FRMD_ID,
         CLAIM.M_TAX_ORG_ADM_INFO_DST_ID

FROM            R_DECL_DEB_REFUND           CLAIM
LEFT OUTER JOIN M_TAX_ORG_ADM_INFO          TAX_ORG_ADM_INFO_SRC      ON CLAIM.M_TAX_ORG_ADM_INFO_SRC_ID      = TAX_ORG_ADM_INFO_SRC.M_TAX_ORG_ADM_INFO_ID
LEFT OUTER JOIN M_TAX_ORG                   TAX_ORG_SRC               ON TAX_ORG_ADM_INFO_SRC.M_TAX_ORG_ID    = TAX_ORG_SRC.M_TAX_ORG_ID
LEFT OUTER JOIN V_TP_NAME_INFO              TP_INFO                   ON CLAIM.R_TAX_PAYER_DECLARANT_ID       = TP_INFO.R_TAX_PAYER_ID
LEFT OUTER JOIN M_KBK                       KBK_SRC                   ON KBK_SRC.M_KBK_ID                     = CLAIM.M_KBK_SRC_ID
LEFT OUTER JOIN M_ASSIGNMENT_PAYMENT        ASSIGN_PAYMENT            ON CLAIM.M_ASSIGNMENT_PAYMENT_ID        = ASSIGN_PAYMENT.M_ASSIGNMENT_PAYMENT_ID
LEFT OUTER JOIN M_KBK                       KBK_DST                   ON CLAIM.M_KBK_DST_ID                   = KBK_DST.M_KBK_ID
LEFT OUTER JOIN M_OPERATION_TYPE            OPERATION_TYPE_DST        ON CLAIM.M_OPERATION_TYPE_DST_ID        = OPERATION_TYPE_DST.M_OPERATION_TYPE_ID
LEFT OUTER JOIN M_TAX_ORG_ADM_INFO          TAX_ORG_ADM_INFO_DST      ON CLAIM.M_TAX_ORG_ADM_INFO_DST_ID      = TAX_ORG_ADM_INFO_DST.M_TAX_ORG_ADM_INFO_ID
LEFT OUTER JOIN M_TAX_ORG                   TAX_ORG_DST               ON TAX_ORG_ADM_INFO_DST.M_TAX_ORG_ID    = TAX_ORG_DST.M_TAX_ORG_ID



LEFT OUTER JOIN (select bank.m_bank_id, max(bank.bik) as bik, max(head_bank.bik) as head_bik
                from m_bank bank
                left outer join r_tax_payer   tp        on bank.r_tax_payer_id          = tp.r_tax_payer_id
                left outer join r_tp_main     tp_main   on tp.r_tp_main_id              = tp_main.r_tp_main_id
                left outer join m_bank        head_bank on tp_main.r_tax_payer_head_id  = head_bank.r_tax_payer_id
                group by bank.m_bank_id)    bank                      on claim.m_bank_id                      = bank.m_bank_id
left outer join r_decl_deb_court            court                     on court.r_decl_deb_refund_id           = CLAIM.r_decl_deb_refund_id

WHERE CLAIM.M_TAX_ORG_FRMD_ID=1 AND CLAIM.M_TAX_ORG_ADM_INFO_DST_ID = 983
ORDER BY CLAIM.DATE_DECL, claim.num_declaration;


Вот отрезок результата:
62693a3533db3988861583.jpeg

И есть такой запрос который выбирает по условию where только тут 6 записей. Но значение столбцов отличаются:
CLAIM.M_TAX_ORG_FRMD_ID = 1 и CLAIM.M_TAX_ORG_ADM_INFO_DST_ID=983 <---- То есть все 6 записей имеют такие значения
Вот отрезок результата:
62693a61dac92463688890.jpeg

Второй запрос

SELECT
        CLAIM.R_DECL_DEB_REFUND_ID,
        CLAIM.NUM_DECLARATION,
        CLAIM.DATE_DECL,
        claim.statement_type,
        claim.source_system,
        TAX_ORG_SRC.CODE_NK,
        TAX_ORG_SRC.CODE_TPK,
        tp_info.iin_bin,
        tp_info.rnn,
        TP_INFO.NAME_R,
        TP_INFO.NAME_K,
        TP_INFO.NAME_E,
        TP_INFO.TYPE_TP,
        KBK_SRC.KBK_CODE,
        nvl(bank.bik, bank.head_bik),
        claim.bank_account_ret,
        kbk_dst.kbk_code,
        operation_type_dst.name_r,
        operation_type_dst.name_k,
        TAX_ORG_ADM_INFO_DST.iin_bin,
        TAX_ORG_ADM_INFO_DST.RNN,
        CLAIM.AMOUNT,
        assign_payment.payment_code,
        claim.status_decl,
        claim.date_process,
        CLAIM.ERROR_DESC,
        CLAIM.DECLINE_CODE,
         court.judge,
         court.doc_num,
         CLAIM.M_TAX_ORG_FRMD_ID,
         CLAIM.M_TAX_ORG_ADM_INFO_DST_ID

FROM            R_DECL_DEB_REFUND           CLAIM
LEFT OUTER JOIN M_TAX_ORG_ADM_INFO          TAX_ORG_ADM_INFO_SRC      ON CLAIM.M_TAX_ORG_ADM_INFO_SRC_ID      = TAX_ORG_ADM_INFO_SRC.M_TAX_ORG_ADM_INFO_ID
LEFT OUTER JOIN M_TAX_ORG                   TAX_ORG_SRC               ON TAX_ORG_ADM_INFO_SRC.M_TAX_ORG_ID    = TAX_ORG_SRC.M_TAX_ORG_ID
LEFT OUTER JOIN V_TP_NAME_INFO              TP_INFO                   ON CLAIM.R_TAX_PAYER_DECLARANT_ID       = TP_INFO.R_TAX_PAYER_ID
LEFT OUTER JOIN M_KBK                       KBK_SRC                   ON KBK_SRC.M_KBK_ID                     = CLAIM.M_KBK_SRC_ID
LEFT OUTER JOIN M_ASSIGNMENT_PAYMENT        ASSIGN_PAYMENT            ON CLAIM.M_ASSIGNMENT_PAYMENT_ID        = ASSIGN_PAYMENT.M_ASSIGNMENT_PAYMENT_ID
LEFT OUTER JOIN M_KBK                       KBK_DST                   ON CLAIM.M_KBK_DST_ID                   = KBK_DST.M_KBK_ID
LEFT OUTER JOIN M_OPERATION_TYPE            OPERATION_TYPE_DST        ON CLAIM.M_OPERATION_TYPE_DST_ID        = OPERATION_TYPE_DST.M_OPERATION_TYPE_ID
LEFT OUTER JOIN M_TAX_ORG_ADM_INFO          TAX_ORG_ADM_INFO_DST      ON CLAIM.M_TAX_ORG_ADM_INFO_DST_ID      = TAX_ORG_ADM_INFO_DST.M_TAX_ORG_ADM_INFO_ID
LEFT OUTER JOIN M_TAX_ORG                   TAX_ORG_DST               ON TAX_ORG_ADM_INFO_DST.M_TAX_ORG_ID    = TAX_ORG_DST.M_TAX_ORG_ID



LEFT OUTER JOIN (select bank.m_bank_id, max(bank.bik) as bik, max(head_bank.bik) as head_bik
                from m_bank bank
                left outer join r_tax_payer   tp        on bank.r_tax_payer_id          = tp.r_tax_payer_id
                left outer join r_tp_main     tp_main   on tp.r_tp_main_id              = tp_main.r_tp_main_id
                left outer join m_bank        head_bank on tp_main.r_tax_payer_head_id  = head_bank.r_tax_payer_id
                group by bank.m_bank_id)    bank                      on claim.m_bank_id                      = bank.m_bank_id
left outer join r_decl_deb_court            court                     on court.r_decl_deb_refund_id           = CLAIM.r_decl_deb_refund_id

WHERE CLAIM.M_TAX_ORG_FRMD_ID=1 AND CLAIM.M_TAX_ORG_ADM_INFO_DST_ID = 983
ORDER BY CLAIM.DATE_DECL, claim.num_declaration;



Так вот как их можно логически связать так чтобы они соединись. В итоге чтобы было 3518 записей
  • Вопрос задан
  • 95 просмотров
Пригласить эксперта
Ответы на вопрос 1
@Akela_wolf
Extreme Programmer
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы