On 6-2-2014 10:54, [email protected] 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
