SQL query does not return records where the corresponding record is inactive
I have three Account
ABC, DEF and XYZ. ABC have two inactive Contracts
. DEF has no contract. XYZ has two contracts (one active and one inactive).
The following query returns me the output as follows.
╔════╦══════════════╦══════╗ ║name║ accountId ║Count ║ ╠════╬══════════════╬══════╣ ║DEF ║ 554-050-4876 ║ 0 ║ ║XYZ ║ 111-000-4345 ║ 1 ║ ╚════╩══════════════╩══════╝
But I expect the result to be as follows:
╔════╦══════════════╦══════╗ ║name║ accountId ║Count ║ ╠════╬══════════════╬══════╣ ║ABC ║ 244-5677-444 ║ 0 ║ ║DEF ║ 554-050-4876 ║ 0 ║ ║XYZ ║ 111-000-4345 ║ 1 ║ ╚════╩══════════════╩══════╝
This means that the request should return everything Accounts
with the number of active ones Contracts
. In the absence of a Contract on the account or only inactive contracts are theirs. The query should return 0 in the 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.
source to share
Move all criteria JOIN
to offer ON
: at this point, you have filters in WHERE
that override yours left outer join
.
eg.
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;
source to share