Mixed results for accounting query

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');

---

Tried afew more variations, but got no where, I'm afraid to say this is above me ;/ Any one got any suggestions on what to try ?

Well there's such an amazinly short dead line on this software that i couldn't wait, i had to change it to having static fields with the balance and just update them on any changes.

For knowledge sake i would like to know what was wrong though if anyone has any pointers ?

Regards,
Kieran

I simplified the problem a bite to see if this is what your after. You can tell me if I'm on the right track or not.

Code SQL:

---

CREATE TABLE accounts(
	aid MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT
	,PRIMARY KEY(aid)
) ENGINE=MyISAM;
 
CREATE TABLE account_transactions (
    tid MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT
	,aid MEDIUMINT UNSIGNED NOT NULL
	,ledger TINYINT UNSIGNED NOT NULL
	,amount DECIMAL(20,2) NOT NULL DEFAULT '0.00'
	,PRIMARY KEY(tid)
	,INDEX(aid)
	,INDEX(ledger)
) ENGINE=MyISAM;
 
CREATE TABLE account_transaction_reverse (
	rid MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT
	,tid MEDIUMINT UNSIGNED NOT NULL
	,PRIMARY KEY(rid)
	,INDEX(tid)
) ENGINE=MyISAM;
 
CREATE TABLE ledger_types (
	lid TINYINT UNSIGNED NOT NULL AUTO_INCREMENT
	,name VARCHAR(24) NOT NULL
	,PRIMARY KEY(lid)
	,INDEX(lid)
) ENGINE=MyISAM;

---

On major change I mad is added a ledger types tables that will hold all the ledger types. This way you can cross join on that table for every account.

Given the below schema I used this query:

Code SQL:

---

SELECT
     acct.aid
     ,COALESCE(SUM(trans.amount),0) AS ledger_total
     ,GROUP_CONCAT(CONCAT(ledger.name,':',COALESCE(trans.amount,0))) AS ledgers
  FROM
      accounts acct
 CROSS
  JOIN
     ledger_types ledger
  LEFT
  JOIN
      (SELECT
            trans.aid
            ,trans.ledger
            ,SUM(trans.amount) AS amount
        FROM
           account_transactions trans
        LEFT
        JOIN 
            account_transaction_reverse rev
          ON
            trans.tid = rev.tid
       WHERE
            rev.rid IS NULL
       GROUP
          BY
           trans.aid
           ,trans.ledger) trans
   ON
     acct.aid = trans.aid 
  AND
     ledger.lid = trans.ledger
GROUP
   BY
     acct.aid

---

This an example of the results:

is that what your looking for?