CODEWITHSUNDEEP
sql-servert-sql
priyanka.sarkar
priyanka.sarkar

Reputation: 26498

Equivalent join clause for Not In clause for the below query

I have the below query

select 
    'Un-contacted Cases'as Header, COUNT(a.Id) as Nos,
    ISNULL(SUM(convert(decimal(18, 2), (isnull(PRINCIPAL_OD, 0)))), 0) as Amt
from 
    LoanAccounts a 
where
    a.Id not in (select b.AccountId  
                 from Feedback b 
                 where OutcomeCurrentAction in ('ptp','Contact new PTP','Contact- No new PTP','Contact- No PTP','Met-No-PTP','New PTP','Payment/Direct Deposit') 
                )

This returns this output:

Header              Nos      Amt
----------------------------------------
Un-contacted Cases  1149    131942525.00

While trying to write the same query using left join, however, I am getting different output

select  
    'Un-contacted Cases' as Header, COUNT(a.Id) as Nos,
    ISNULL(SUM(convert(decimal(18, 2), (isnull(PRINCIPAL_OD, 0)))), 0) as Amt
from 
    LoanAccounts a 
left join 
    Feedback b on b.AccountId = a.Id 
where 
    OutcomeCurrentAction in ('ptp','Contact new PTP','Contact- No new PTP','Contact- No PTP','Met-No-PTP','New PTP','Payment/Direct Deposit')

This returns this output:

Header              Nos      Amt
---------------------------------------
Un-contacted Cases  51      6026486.00

What can be an equivalent JOIN for the above Not In - ?

Upvotes: 0

Views: 82

Answers (3)

blackrussian
blackrussian

Reputation: 73

If you are doing a left join on table b, you can't put a filter on a row in table b into your where clause (unless it is to look for a null value). The where clause essentially turns your left join into an inner join, since a non existent row in table b is always going to have null field values.

So you need to put the where clause content into the left join criteria. Something like this:

select  
'Un-contacted Cases' as Header, COUNT(a.Id) as Nos,
ISNULL(SUM(convert(decimal(18, 2), (isnull(PRINCIPAL_OD, 0)))), 0) as Amt
from 
LoanAccounts a 
left join 
Feedback b on b.AccountId = a.Id 
and
b.OutcomeCurrentAction in ('ptp','Contact new PTP','Contact- No new PTP','Contact- No PTP','Met-No-PTP','New PTP','Payment/Direct Deposit')
where b.AccountId is null

This is essentially the same answer as Gordon, but just to explain why.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269483

It would look like:

select 'Un-contacted Cases'as Header, COUNT(a.Id) as Nos,
       COALESCE(SUM(convert(decimal(18, 2), PRINCIPAL_OD)), 0) as Amt
from LoanAccounts a left join
     Feedback b
     on b.AccountId = a.Id and 
        b.OutcomeCurrentAction 
     in ('ptp','Contact new PTP','Contact- No new PTP','Contact- No PTP','Met-No-PTP','New PTP','Payment/Direct Deposit') 
where b.AccountId is null;

I also fixed your SUM(). Your version is more complicated than necessary. I actually think you really want:

       convert(decimal(18, 2) coalesce(sum(PRINCIPAL_OD), 0) as Amt

When doing arithmetic on decimals, it is better to convert after the arithmetic -- if that is the data type you really want.

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

you can try like below

select 'Un-contacted Cases'as Header, COUNT(a.Id) as Nos,ISNULL(SUM(convert(decimal(18,2),(isnull(PRINCIPAL_OD,0)))),0) as Amt
from LoanAccounts a
left join
(
select b.AccountId  from Feedback b 
    where   OutcomeCurrentAction in ('ptp','Contact new PTP','Contact- No new PTP','Contact- No PTP','Met-No-PTP','New PTP','Payment/Direct Deposit') 
) a1 on a.Id =a1.AccountId  
  where a1.AccountId  is null

Upvotes: 1

Related Questions