title: How to replace sub query with join in this case when there are different conditions in where clause?
body: <p>I have two tables. <code>led</code> and <code>Tbl_LoanMst</code> </p>
  
  <pre><code>CREATE TABLE [dbo].[led](
      [GLCode] [varchar](10) NOT NULL,
      [AccountID] [varchar](15) NULL,
      [Dr_Cr] [char](1) NOT NULL,
      [amount] [money] NULL,
      [LoanRefNo] [varchar](20) NULL
  ) ON [PRIMARY]
  
  CREATE TABLE [dbo].[Tbl_LoanMst](
      [SrNo] [int] IDENTITY(1,1) NOT NULL,
      [Acctno] [varchar](15) NULL,
      [LoanTranchNo] [varchar](20) NULL,
      [DisbursalDate] [varchar](8) NULL,
      [LoanStartDate] [varchar](8) NULL,
      [Tenure] [money] NULL,
      [LoanExpiryDate] [varchar](8) NULL,
      [InterestRate] [money] NULL,
      [PenalInterestRate] [money] NULL,
      [IntrFrequency] [char](11) NULL,
      [LoanAmount] [money] NULL,
      [ApplicableDP] [money] NULL,
      [ProcessingFees] [money] NULL,
      [OtherCharges] [money] NULL,
      [Paymode] [varchar](6) NULL,
      [Chq_BankTxnNo] [varchar](15) NULL,
      [CompanyBankID] [varchar](20) NULL,
      [CompanyBankAcctno] [varchar](20) NULL,
      [Cl_BankID] [varchar](10) NULL,
      [Cl_BankAcctno] [varchar](20) NULL,
      [LedgerRefNo] [varchar](20) NULL,
      [Status] [char](1) NULL,
      [uMkrid] [varchar](11) NULL,
      [uMkrdt] [datetime] NULL,
      [aMkrid] [varchar](11) NULL,
      [aMkrdt] [datetime] NULL,
      [BF_Generated] [varchar](5) NULL,
      [bankaccttype] [varchar](20) NULL,
      [LoanType] [varchar](20) NULL
  ) ON [PRIMARY]
  </code></pre>
  
  <p>This is my working query. </p>
  
  <pre><code>declare @LasAcctNo varchar(20)  = 'las00004'      
  
  select distinct LoanTranchNo,  convert(varchar,convert(datetime,LoanStartDate),103) LoanStartDate,
  
  (select CONVERT(numeric(18,2),ROUND( sum(amount),2)) from  led where  GLCode = 'LASPAC' and LoanRefNo = LoanTranchNo and Dr_Cr='D' ) as LoanAmount,
  (select CONVERT(numeric(18,2),ROUND( sum(amount),2)) from  led where  GLCode = 'LASPAC' and LoanRefNo = LoanTranchNo and Dr_Cr='C' ) PrincipalPaid
  , (select CONVERT(numeric(18,2),ROUND( sum(amount),2)) from  led where  GLCode = 'LASPAC' and LoanRefNo = LoanTranchNo) 'OsLoanAmount',
  InterestRate,
  (select CONVERT(numeric(18,2),ROUND( sum( case when Dr_Cr='C' then amount *-1 else amount  end  ),2)) from  led where GLCode = 'INTRAC' and LoanRefNo = LoanTranchNo)  InterestAccruedDue,
  (select CONVERT(numeric(18,2),ROUND( sum(case when Dr_Cr='C' then amount *-1 else amount  end ),2)) from  led where  GLCode = 'PNLINT' and LoanRefNo = LoanTranchNo)  PenalDue ,0 as 'TotalOutstanding',
  (select CONVERT(numeric(18,2),ROUND( sum(case when Dr_Cr='C' then amount *-1 else amount  end ),2)) from  led where  GLCode = 'INTRND' and LoanRefNo = LoanTranchNo) InterestAccruedbutnotdue,
  (select CONVERT(numeric(18,2),ROUND( sum(case when Dr_Cr='C' then amount *-1 else amount  end ),2)) from  led where  GLCode = 'TDSACC' and LoanRefNo = LoanTranchNo)as  TDS,
  (select CONVERT(numeric(18,2),ROUND( sum(case when Dr_Cr='C' then amount *-1 else amount  end ),2)) from  led where  GLCode = 'CLNCTRLAC' and AccountID = Acctno)as  ControlAcct,
  convert(varchar,convert(datetime,LoanExpiryDate),103) LoanEndDate,IntrFrequency,isnull(LoanType,'SECURED')  as LoanType
  from tbl_loanmst
  left join  led on LoanTranchNo= LoanRefNo and AcctNo = AccountID
  where AcctNo= @LasAcctNo and status='A'
  </code></pre>
  
  <p>Is it possible to rewrite this query using joins? if yes how?</p>
  
  <p>In order to make things as convenient as possible for you I am included  insert statements on the following link:
  <a href="http://en.textsave.org/mwOb" rel="nofollow">http://en.textsave.org/mwOb</a></p>