sql - How to use IF EXISTS in join statement -


select      *      ledger led  left outer join     (if exists(select accountcode b_c_j_trans accountcode = '001809' , companycode='ram' --and entryno='25'      , mode='j')          select companycode, sum(amount * 0) amount         b_c_j_trans bcjtrans         (companycode = 'ram') , (mode = 'j')          group companycode     else         select companycode, sum( case when bcjtrans.accountcode = '800100' bcjtrans.amount else 0 end) amount         b_c_j_trans bcjtrans         (companycode = 'ram')  , (mode = 'j')         group companycode) on led.companycode = a.companycode                                     , led.bookcode = a.bookcode                                     , led.[type] = a.mode                                     , led.financialyear = a.entryyear                                     , led.voucherno = a.entryno       led.companycode = 'ram'      , led.acccode = '800100'      , led.voucherdate >= '2015-04-01'      , led.voucherdate <= '2015-05-31' 

this query want use if exist in left outer join of ledger table

move exists where clause

select *   ledger led        left outer join (select companycode,                                sum(amount * 0) amount                           b_c_j_trans bcjtrans                          ( companycode = 'ram' )                                , ( mode = 'j' )                                , exists(select accountcode                                             b_c_j_trans                                            accountcode = '001809'                                                  , companycode = 'ram' --and entryno='25'                                                   , mode = 'j')                         group  companycode                         union                         select companycode,                                sum(case                                      when bcjtrans.accountcode = '800100' bcjtrans.amount                                      else 0                                    end) amount                           b_c_j_trans bcjtrans                          ( companycode = 'ram' )                                , ( mode = 'j' )                                , not exists(select accountcode                                                 b_c_j_trans                                                accountcode = '001809'                                                      , companycode = 'ram' --and entryno='25'                                                       , mode = 'j')                         group  companycode)as                     on led.companycode = a.companycode                        , led.bookcode = a.bookcode                        , led.[type] = a.mode                        , led.financialyear = a.entryyear                        , led.voucherno = a.entryno  led.companycode = 'ram'        , led.acccode = '800100'        , led.voucherdate >= '2015-04-01'        , led.voucherdate <= '2015-05-31'  

am sure code can simplified if share original logic


Comments