SQL query failing to include NULL values

catching_streams

Hi there,

My query attempts to list all subaccount balances in an account where any subaccount begins with 777 or 888.

Accounts are structured as having any number of Debit and/or Credit subaccounts.

The problem I am having is when there is no balance for a particular subaccount the query is not returning NULL for that particular account, it is 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, however I am unsure as to how to rewrite it!

SELECT c.name	 "Company", 
acc.name	 "Account", 
sa.name     	 "SubAccount", 
b.balance	 "Balance"

AccSide = 
CASE acc.AccSide 
WHEN 001 THEN 'Debit'
WHEN 002 THEN 'Credit' 
END 

FROM 
SubAccount sa WITH JOIN 
SubAccount sa WITH ON sa.accId = acc.Id LEFT JOIN 
AccountBalance b WITH ON sa.ID = b.accID JOIN 
Company c WITH ON c.ID = sa.compID 

WHERE b.date = 
( 
SELECT MAX(b2.date) 
FROM accountbalance b2
WHERE accountid = sa.id 
) 

AND acc.id IN ( 
SELECT acc.id 
FROM 
acc join SubAccount ON 
subaccount.accid = acc.id 
WHERE SubAccount.name like '777%' or SubAccount.name like '888%') 

ORDER BY Account, SubAccount

Assistance is greatly appreciated

DonnieBrasco

i think you need to outer join on AccountBalance with SubAccount



SELECT c.name "Company",
acc.name "Account",
sa.name "SubAccount",
b.balance "Balance"

AccSide =
CASE acc.AccSide
WHEN 001 THEN 'Debit'
WHEN 002 THEN 'Credit'
END

FROM
SubAccount sa WITH JOIN
SubAccount sa WITH ON sa.accId = acc.Id LEFT OUTER JOIN
AccountBalance b WITH ON sa.ID = b.accID JOIN
Company c WITH ON c.ID = sa.compID

WHERE b.date =
(
SELECT MAX(b2.date)
FROM accountbalance b2
WHERE accountid = sa.id
)

AND acc.id IN (
SELECT acc.id
FROM
acc join SubAccount ON
subaccount.accid = acc.id
WHERE SubAccount.name like '777%' or SubAccount.name like '888%')

ORDER BY Account, SubAccount

bonkey

I dunno this particular flavour of SQL....but...

As a general rule, the WHERE clause in a query is applied after the JOINs have occurred.

You're outer-joining AccountBalance, which will ensure that you keep all records from sa, even when there isn't a matching AccountBalance record.

So far so good. SO...for any sa record without a matching ab record, all ab records are NULL.

Then the WHERE clause is applied, and searches for all records with the max(date)....which promptly removes all the NULL (non-matching) cases the outer join generated.

The solution, I think, will be to move the date-check from the WHERE clause into the ON clause of the join to Account Balance.

...
LEFT JOIN AccountBalance b
WITH ON sa.ID = b.accID
AND b.date = (
SELECT MAX(b2.date)
FROM accountbalance b2
WHERE accountid = sa.id
)
JOIN Company c WITH ON c.ID = sa.compID

WHERE acc.id IN (
...

catching_streams

Cheers Bonkey. I'll alter the code and let you know how I get on.

@Donnie - I believe the OUTER is an optional keyword and it's what I was using intentionally. Thanks for looking though.

catching_streams

@Bonkey, cheers for the tweak, now runs as required.