On 6-2-2014 10:54, gorvian@... wrote:

> This is my query
>
> select A.internal,A.surname,Sum(C.amount)from Accounts A inner join
> Accountfinancial B on A.identifier = B.accountlink
> left outer join Accountitems C on B.identifier = C.account
> where C.itemtype='Debit'
> group by A.internal,A.surname
> order by A.internal
>
> For every record in the Accounts table there is a matching record in
> Accountfinancial but there might not be any transactions posted for some
> accounts.
>
> I need to list every single record in accounts and include the ones that
> has no linked transactions in Accountitems.
>
> But I only get the result for Accounts with transactions in Accountitems
> and not the ones without ( 0 Balance accounts )

The problem is your WHERE clause:
C.itemtype='Debit'

For accounts without transactions, C.itemtype is NULL, so this condition
filters out all those rows.

You either need to change the WHERE clause to:
C.itemtype='Debit' OR C.itemtype IS NULL

This will include accounts without transaction, but exclude accounts
that have transactions, but no transactions with itemtype 'Debit'.

When you move the condition to the JOIN condition:
on B.identifier = C.account AND C.itemtype='Debit

This will have the results that Accounts that do have transactions, but
none with itemtype 'Debit' will also be included.

Mark
--
Mark Rotteveel