How to replace sub query with join in this case when there are different conditions in where clause?

Question

I have two tables. led and Tbl_LoanMst

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]

This is my working query.

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'

Is it possible to rewrite this query using joins? if yes how?

In order to make things as convenient as possible for you I am included insert statements on the following link: http://en.textsave.org/mwOb

Hi. You're already using a join. Could you let us know what problem you're trying to solve, or is it a general style improvement, or ...? — wwkudu, Dec 04 '15 at 06:05
@wwkudu I am just trying to learn by doing this. I was wondering if it would lead to any performance improvement if i am able to rewrite this `select column1, (select ......) as column2 from table...` as `select column1, table.column2 from table join table2` even if there is no performance improvement i would still like to learn a new style. — SamuraiJack, Dec 04 '15 at 06:11
Cool. The two ways of doing joins are `select columns from t1, t2 where t1.foreign_key=t2.primary_key` and `select columns from t1 left join t2 on t1.foreign_key=t2.primary_key`. In most databases, they are equivalent in performance. Of course they don't have to be just foreign keys and primary keys. You're already using the latter. The former is only an inner join if I'm thinking straight. The latter has lots more options, a diagram explaining which, is [here](http://stackoverflow.com/questions/5706437/whats-the-difference-between-inner-join-left-join-right-join-and-full-join). — wwkudu, Dec 04 '15 at 07:02
Hi @Arbaaz, I've just offered an answer for you. One piece of advice for StackOverflow: make your example code as simple as possible to illustrate what you need. I think you might have had a response sooner if your example query was shorter; for example, only include a few of the columns you're trying to do the same thing to, and cut down the data you linked to, to only include rows that your example query uses. — Stuart J Cuthbertson, Dec 15 '15 at 23:40

score 0 · Answer 1 · answered Dec 15 '15 at 23:37

Since you suggest in comments that you're keen to learn, not just to copy, I'm intentionally not providing a full solution :-)

There are almost certainly multiple ways this can be achieved without subqueries; I commend your desire to learn an alternative, as I personally dislike subqueries in most circumstances (certainly this one).

You're already familiar with using case within an aggregate function like sum, so an easy way to remove your subqueries is to extend that pattern. Picking just one subquery as an example:

(select CONVERT(numeric(18,2),ROUND( sum(amount),2)) from  led where  GLCode = 'LASPAC' and LoanRefNo = LoanTranchNo and Dr_Cr='D' ) as LoanAmount,

this could be rewritten by moving the relevant where criteria into a case statement:

,convert(numeric(18,2),round(sum(case when L.GLCode = 'LASPAC' and L.Dr_Cr='D' then amount end),2)) as LoanAmount

because you can refer directly to any columns from the led table, because you've already joined to it. The check LoanRefNo = LoanTranchNo isn't needed in this form, because it's part of the main join criteria between the two tables.

This works partly because the new case statement will be null when the condition isn't met, so the sum won't include the nulls in the calculation.

To make this work in a wider query, you do need to use a SQL group by clause to allow the sum function to operate over multiple rows. The group by tells SQL which columns to group together to produce sums of numbers.

So, an example with a bit more context:

with cteGroupedData as (
  select          
              T.LoanTranchNo
              ,T.InterestRate
              ,T.IntrFrequency
              ,T.LoanStartDate
              ,T.LoanExpiryDate

              ,sum(case when L.GLCode = 'LASPAC' and L.Dr_Cr='D' then amount end) as LoanAmount

  from        tbl_loanmst    T
  left join   led            L    on T.LoanTranchNo=L.LoanRefNo
                                  and T.AcctNo = L.AccountID
  where         T.Acctno= 'las00004'
  and           T.Status='A'

  group by    T.LoanTranchNo
              ,T.InterestRate
              ,T.IntrFrequency
              ,T.LoanStartDate
              ,T.LoanExpiryDate
  )

select    LoanTranchNo
          ,InterestRate
          ,IntrFrequency
          ,convert(varchar,convert(datetime,LoanStartDate),103)     as LoanStartDate
          ,convert(varchar,convert(datetime,LoanExpiryDate),103)    as LoanEndDate

          ,convert(numeric(18,2),round(LoanAmount,2))             as LoanAmount

from      cteGroupedData

This example is just using a few of your columns (because I had to cut it down to understand it myself) so you'll need to use it as an example and build back up to your full query.

Other points to note

I've done some other things here that I'll point out briefly:

Separated the main grouping step into a Common Table Expression (CTE) - this makes it clearer in my opinion, and means the datatype conversions can be done separately later on.
Aliased the tables as T and L, and used these aliases everywhere, so I know easily which table each column comes from
Spaced it out the way I like it - personal preference, but space is your friend!

How to replace sub query with join in this case when there are different conditions in where clause?

1 Answers1

Other points to note