It's my first post and i'm hoping you guys can assist me!
This query essentially lists of all the latest account balances in an accountpool where accounts begin with 777 and 888.
The accountpool lies within a company and 'id' is the primary key for all of these tables.
The problem I am having is when there is no balance for a particular a/c the query is not returning NULL for that particular account, it is completely ignoring it. I would like it to return NULL
I believe the problem lies in the WHERE part of the query when no accountid is returned.
SELECT c.name "Company",
CONVERT (VARCHAR(12), b.datex, 103) "Date",
WHEN -30001 THEN 'WE'
WHEN -30002 THEN 'THEY'
AccountPool ap WITH JOIN
account a WITH ON a.PoolId = ap.Id LEFT JOIN
accountbalance b WITH ON a.ID = b.accountID JOIN
company c WITH ON c.ID = a.companyID
WHERE b.datex =
FROM accountbalance b2 WITH (NOLOCK)
WHERE accountid = a.id
AND ap.id IN (
accountpool join account ON
account.poolid = accountpool.id
WHERE account.name like '7777%' or account.name like '8888%')
ORDER BY AccountPool, Account