I'm doing a bit of accountancy software and i have a function to get some search results and hand it to an object dedicated for holding account search data.

My query will return key information about the accounts, as well as a a balance for each of the three ledgers (client ledger, office ledger, bill ledger). All it's doing really is pulling any transaction in that ledger that is confirmed and is not reversed.

The issue i'm having is it will return the correct client balance for "Mr Kieran Peat (no delete)", but not for anyone else :S
Actuallly, just figured if there's confirmed transactions from other accounts, it'll add the amount onto the client ledger for "Mr Kieran Peat (no delete)"

Well confused :S any suggestions ?

Code MySQL:
SELECT account.aid
	 , account.manager_id
	 , manager.prefix				as manager_prefix
	 , manager.forename			as manager_forename
	 , manager.surname			as manager_surname
	 , account.type
	 , price.amount
	 , price.vat
	 , account.name
	 , account.prefix
	 , account.forename
	 , account.surname
	 , account.created
	 , account.updated
	 , account.closed
	 , client_ledger.amount		as client_amount
	 , client_ledger.vat		as client_vat
	 , office_ledger.amount		as office_amount
	 , office_ledger.vat		as office_vat
	 , bill_ledger.amount		as bill_amount
	 , bill_ledger.vat			as bill_vat
FROM accounts as account
LEFT OUTER
  JOIN users as manager
	ON manager.id = account.manager_id
LEFT OUTER
  JOIN account_monitors as monitors
	ON monitors.aid = account.aid
LEFT OUTER
  JOIN account_price as price
	ON price.price = account.price
LEFT OUTER JOIN
(
	SELECT c_ledger.aid
		 , c_ledger.ledger
		 , c_ledger.confirmed_by
		 , SUM(c_ledger.amount) as amount
		 , SUM(c_ledger.vat) as vat
	FROM account_transactions as c_ledger
	LEFT OUTER
	  JOIN account_transaction_reverse as c_reverse
		ON c_reverse.tid = c_ledger.tid
	WHERE c_reverse.uid IS NULL AND c_ledger.confirmed_by > 0
) AS client_ledger
  ON client_ledger.aid = account.aid AND client_ledger.ledger = 1
LEFT OUTER JOIN
(
	SELECT o_ledger.aid
		 , o_ledger.ledger
		 , o_ledger.confirmed_by
		 , SUM(o_ledger.amount) as amount
		 , SUM(o_ledger.vat) as vat
	FROM account_transactions as o_ledger
	LEFT OUTER
	  JOIN account_transaction_reverse as o_reverse
		ON o_reverse.tid = o_ledger.tid
	WHERE o_reverse.uid IS NULL AND o_ledger.confirmed_by > 0
) AS office_ledger
  ON office_ledger.aid = account.aid AND office_ledger.ledger = 2
LEFT OUTER JOIN
(
	SELECT b_ledger.aid
		 , b_ledger.ledger
		 , b_ledger.confirmed_by
		 , SUM(b_ledger.amount) as amount
		 , SUM(b_ledger.vat) as vat
	FROM account_transactions as b_ledger
	LEFT OUTER
	  JOIN account_transaction_reverse as b_reverse
		ON b_reverse.tid = b_ledger.tid
	WHERE b_reverse.uid IS NULL AND b_ledger.confirmed_by > 0
) AS bill_ledger
  ON bill_ledger.aid = account.aid AND office_ledger.ledger = 3
 
GROUP BY account.aid
ORDER BY account.created DESC
LIMIT 0, 10

Test data

Code MySQL:
-- phpMyAdmin SQL Dump
-- version 3.2.0.1
-- [url]http://www.phpmyadmin.net[/url]
--
-- Host: localhost
-- Generation Time: Oct 21, 2009 at 09:03 AM
-- Server version: 5.1.36
-- PHP Version: 5.3.0
 
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
 
--
-- Database: `wolstenholmes`
--
 
-- --------------------------------------------------------
 
--
-- Table structure for table `accounts`
--
 
CREATE TABLE IF NOT EXISTS `accounts` (
  `aid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `manager_id` smallint(5) unsigned DEFAULT '0',
  `stream_id` int(11) unsigned DEFAULT '0',
  `old_aid` varchar(25) DEFAULT '',
  `old_fileid` varchar(25) DEFAULT '',
  `old_cid` varchar(25) DEFAULT '',
  `old_ref` varchar(25) DEFAULT '',
  `type` tinyint(2) DEFAULT '0',
  `price` int(15) unsigned DEFAULT '0',
  `name` varchar(50) DEFAULT '',
  `prefix` varchar(5) DEFAULT '',
  `forename` varchar(50) DEFAULT '',
  `middlename` varchar(200) DEFAULT '',
  `surname` varchar(50) DEFAULT '',
  `email` varchar(320) DEFAULT '',
  `main_contact` varchar(15) DEFAULT '',
  `street` varchar(255) DEFAULT '',
  `locality` varchar(50) DEFAULT '',
  `region` varchar(50) DEFAULT '',
  `postcode` varchar(10) DEFAULT '',
  `created` int(10) unsigned DEFAULT '0',
  `updated` int(10) unsigned DEFAULT '0',
  `closed` int(10) unsigned DEFAULT '0',
  PRIMARY KEY (`aid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
 
--
-- Dumping data for table `accounts`
--
 
INSERT INTO `accounts` (`aid`, `manager_id`, `stream_id`, `old_aid`, `old_fileid`, `old_cid`, `old_ref`, `type`, `price`, `name`, `prefix`, `forename`, `middlename`, `surname`, `email`, `main_contact`, `street`, `locality`, `region`, `postcode`, `created`, `updated`, `closed`) VALUES
(1, 0, 87, '', '', '', '', 1, 1, 'Mr Kieran  Test (no Delete)', 'Mr', 'Kieran', '', 'Test (no Delete)', 'kieran.peat@wolstenholmes.co.uk', '07960535525', '119 Rutherford Drive', 'Over Hulton', 'Bolton', 'BL5 1DW', 1256029580, 1256029580, 0),
(2, 0, 93, '', '', '', '', 5, 2, 'Mr Kieran  Another Test', 'Mr', 'Kieran', '', 'Another Test', 'kieran.peat@wolstenholmes.co.uk', '07960535525', 'Erger', 'Ergr', 'Ergre', 'BL5 1DW', 1256033535, 1256033535, 0) ;
 
-- --------------------------------------------------------
 
--
-- Table structure for table `account_monitors`
--
 
CREATE TABLE IF NOT EXISTS `account_monitors` (
  `aid` int(10) unsigned NOT NULL DEFAULT '0',
  `user_id` smallint(5) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`aid`,`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
--
-- Dumping data for table `account_monitors`
--
 
 
-- --------------------------------------------------------
 
--
-- Table structure for table `account_price`
--
 
CREATE TABLE IF NOT EXISTS `account_price` (
  `price` int(15) unsigned NOT NULL AUTO_INCREMENT,
  `amount` decimal(10,2) DEFAULT '0.00',
  `vat` decimal(8,2) DEFAULT '0.00',
  PRIMARY KEY (`price`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
 
--
-- Dumping data for table `account_price`
--
 
INSERT INTO `account_price` (`price`, `amount`, `vat`) VALUES
(1, '562.53', '84.00'),
(2, '265.00', '39.75') ;
 
-- --------------------------------------------------------
 
--
-- Table structure for table `account_price_breakdown`
--
 
CREATE TABLE IF NOT EXISTS `account_price_breakdown` (
  `price` int(15) unsigned DEFAULT NULL,
  `name` varchar(50) DEFAULT '',
  `amount` decimal(10,2) DEFAULT '0.00',
  `vat` decimal(6,2) DEFAULT '0.00',
  KEY `price` (`price`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
--
-- Dumping data for table `account_price_breakdown`
--
 
INSERT INTO `account_price_breakdown` (`price`, `name`, `amount`, `vat`) VALUES
(1, 'Sale', '235.00', '35.25'),
(1, 'Client Identification Verification', '6.00', '0.90'),
(1, 'Telegraphic Transfer (TT) Fee', '39.00', '5.85'),
(1, 'Mortgage Fee', '55.00', '8.25'),
(1, 'HIPs', '225.00', '33.75'),
(1, 'Preparation of bank transfer', '2.53', '0.00'),
(2, 'HIPs', '225.00', '33.75'),
(2, 'Leasehold Surcharge', '40.00', '6.00'),
(3, 'Sale', '159.00', '23.85'),
(3, 'Client Identification Verification', '6.00', '0.90'),
(3, 'Telegraphic Transfer (TT) Fee', '39.00', '5.85'),
(3, 'Mortgage Fee', '55.00', '8.25'),
(3, 'Preparation of bank transfer', '2.53', '0.00');
 
-- --------------------------------------------------------
 
--
-- Table structure for table `account_transactions`
--
 
CREATE TABLE IF NOT EXISTS `account_transactions` (
  `tid` int(15) unsigned NOT NULL AUTO_INCREMENT,
  `aid` int(10) unsigned DEFAULT '0',
  `issued_by` smallint(5) unsigned DEFAULT '0',
  `type` tinyint(1) DEFAULT '1',
  `ledger` tinyint(1) DEFAULT '1',
  `method` tinyint(1) DEFAULT '1',
  `amount` decimal(10,2) DEFAULT '0.00',
  `vat` decimal(6,2) DEFAULT '0.00',
  `time` int(10) unsigned DEFAULT '0',
  `contact` varchar(50) DEFAULT '',
  `ref` varchar(30) DEFAULT '',
  `description` text,
  `confirmed_by` smallint(5) unsigned DEFAULT '0',
  `confirmed_time` int(10) unsigned DEFAULT '0',
  PRIMARY KEY (`tid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;
 
--
-- Dumping data for table `account_transactions`
--
 
INSERT INTO `account_transactions` (`tid`, `aid`, `issued_by`, `type`, `ledger`, `method`, `amount`, `vat`, `time`, `contact`, `ref`, `description`, `confirmed_by`, `confirmed_time`) VALUES
(1, 1, 101, 1, 1, 1, '200.00', '0.00', 1256029580, 'Mr Kieran  Test (no Delete)', '', 'Deposit', 101, 1256029629),
(2, 1, 101, 1, 1, 6, '500000.00', '0.00', 1256030533, 'Mortgage lender', '', 'Money from the mortgage lender', 101, 1256030533),
(4, 2, 101, 1, 1, 1, '265.00', '39.75', 1256033535, 'Mr Kieran  Another Test', '', 'Deposit', 0, 0) ;
 
-- --------------------------------------------------------
 
--
-- Table structure for table `account_transaction_reverse`
--
 
CREATE TABLE IF NOT EXISTS `account_transaction_reverse` (
  `tid` int(15) unsigned NOT NULL DEFAULT '0',
  `uid` smallint(5) unsigned DEFAULT '0',
  `time` int(10) unsigned DEFAULT '0',
  `reason` text,
  PRIMARY KEY (`tid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
--
-- Dumping data for table `account_transaction_reverse`
--
 
INSERT INTO `account_transaction_reverse` (`tid`, `uid`, `time`, `reason`) VALUES
(1, 101, 1256115097, 'tyjy');