Advertisement
Help Keep Boards Alive. Support us by going ad free today. See here: https://subscriptions.boards.ie/.
If we do not hit our goal we will be forced to close the site.

Current status: https://keepboardsalive.com/

Annual subs are best for most impact. If you are still undecided on going Ad Free - you can also donate using the Paypal Donate option. All contribution helps. Thank you.
https://www.boards.ie/group/1878-subscribers-forum

Private Group for paid up members of Boards.ie. Join the club.

SQL query failing to include NULL values

  • 06-10-2009 01:24AM
    #1
    catching_streams
    Registered Users, Registered Users 2 Posts: 67 ✭✭


    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


Comments

  • #2
    DonnieBrasco
    Registered Users, Registered Users 2 Posts: 169 ✭✭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


  • #3
    bonkey
    Registered Users, Registered Users 2 Posts: 15,443 ✭✭✭✭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 (
    ...


  • #4
    catching_streams
    Registered Users, Registered Users 2 Posts: 67 ✭✭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.


  • #5
    catching_streams
    Registered Users, Registered Users 2 Posts: 67 ✭✭catching_streams


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


Advertisement