Issue will NULL populated field

Last Post 01 Oct 2009 08:10 AM by dmurphy09. 0 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
dmurphy09
New Member
New Member

--
01 Oct 2009 08:10 AM
Hi there,

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",
ap.name "AccountPool",
a.name "Account",
b.balance "Balance",
CONVERT (VARCHAR(12), b.datex, 103) "Date",
PoolSide =
CASE a.poolside
WHEN -30001 THEN 'WE'
WHEN -30002 THEN 'THEY'
ELSE 'INT'
END
FROM
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 =
(
SELECT MAX(b2.datex)
FROM accountbalance b2 WITH (NOLOCK)
WHERE accountid = a.id
)

AND ap.id IN (
SELECT accountpool.id
FROM
accountpool join account ON
account.poolid = accountpool.id
WHERE account.name like '7777%' or account.name like '8888%')

ORDER BY AccountPool, Account


Acceptable Use Policy
---