Subject Re: Odp: [firebird-support] Left outer join not giving me the result I want
Author Gorvian Black
Post date 2014-02-07T09:56:30Z
Brilliant ! Thanks



On Thursday, February 6, 2014 10:17 PM, "liviuslivius@..." <liviuslivius@...> wrote:
 
Hi,

This is because you add filer in where to resultset move your filter C.itemtype='Debit' from where clause to lefeft join filter and you get what you want

Regards,
Karol Bieniaszewski

----- Reply message -----
Od: gorvian@...
Do: <firebird-support@yahoogroups.com>
Temat: [firebird-support] Left outer join not giving me the result I want
Data: czw., lut 6, 2014 10:54


 
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 )