SQL query not returning records where related record is inactive

Question

I have three Account ABC, DEF and XYZ. ABC have two Inactive Contracts. DEF don't have any contract. XYZ have two contracts (one active and one inactive).

Following query is returning me output as following.

╔════╦══════════════╦══════╗
║name║ accountId    ║Count ║
╠════╬══════════════╬══════╣
║DEF ║ 554-050-4876 ║  0   ║
║XYZ ║ 111-000-4345 ║  1   ║
╚════╩══════════════╩══════╝

But I am expecting the result as follwing:

╔════╦══════════════╦══════╗
║name║ accountId    ║Count ║
╠════╬══════════════╬══════╣
║ABC ║ 244-5677-444 ║  0   ║
║DEF ║ 554-050-4876 ║  0   ║
║XYZ ║ 111-000-4345 ║  1   ║
╚════╩══════════════╩══════╝

Means, query should return all the Accounts with the number of active Contracts. If there is no Contract exist of an account or only inactive contracts are their. Query should return 0 in Count column.

SELECT 
    a.name 
    , a.accountid 
    , COUNT(c.contractid) AS  'Count' --Number Active Of Contracts
FROM FilteredAccount AS a
LEFT OUTER JOIN FilteredContract AS c
    ON a.accountid = c.accountid
WHERE a.statecode = 0 -- Active
    AND a.customertypecode = 3 -- Active
    AND a.name IN ('ABC','XYZ')
    AND (c.statecode = 2 or c.statecode is null)
GROUP BY a.name , a.accountid;

Thanks in Advance.

Answer 1

Move all the JOIN criteria into the ON clause: at the moment you have filtering terms in the WHERE which override your left outer join.

e.g.

SELECT 
    a.name 
    , a.accountid 
    , COUNT(c.contractid) AS  'Count' --Number Active Of Contracts
FROM FilteredAccount AS a
LEFT OUTER JOIN FilteredContract AS c
    ON (c.statecode = 2 or c.statecode is null) and a.accountid = c.accountid
WHERE a.statecode = 0 -- Active
    AND a.customertypecode = 3 -- Active
    AND a.name IN ('ABC','XYZ')
GROUP BY a.name , a.accountid;