I'm quite stumped, i have two different users with clients assigned to them. One has 17 leads two of which are 'confirmed' and the other has 2 clients, both 'confirmed'. But the results of this are the first user has 19 leads apparently and the second has 0 clients except some how got 4 'confirmations'.

If someone could point out if i've made any obvious mistakes that would cause this, that'd be a great help, my knowledge of advanced SQL is abit gappy.


Code MySQL:
SELECT user.id
	 , user.prefix
	 , user.forename
	 , user.surname
 
	 , clients.clients
	 , confirmed.confirmed
	 , paid.paid
	 , deposit.deposit
	 , case_value.case_value			as 'case'
 
	 , discard.count					as discard
	 , callback.count					as callback
FROM user_csb as csb
LEFT OUTER
  JOIN users as user
	ON user.id = csb.id
 
/* GENERAL STATS */
LEFT OUTER JOIN
(
	SELECT count(sub1_client.id) as clients
		 , sub1_client.assigned_id
	FROM conveyancing_clients as sub1_client
	LEFT OUTER
	  JOIN conveyancing_quotes as sub1_quote
		ON sub1_client.id = sub1_quote.id
	WHERE sub1_client.created > 1265846400 AND sub1_client.created < 1266537599
)	AS clients
	ON clients.assigned_id = csb.id
LEFT OUTER JOIN
(
	SELECT count(sub2_client.id) as confirmed
		 , sub2_client.assigned_id
	FROM conveyancing_clients as sub2_client
	LEFT OUTER
	  JOIN conveyancing_quotes as sub2_quote
		ON sub2_client.id = sub2_quote.id
	WHERE sub2_quote.confirm_time > 1265846400 AND sub2_quote.confirm_time < 1266537599
)	AS confirmed
	ON confirmed.assigned_id = csb.id
LEFT OUTER JOIN
(
	SELECT count(sub3_client.id) as paid
		 , sub3_client.assigned_id
	FROM conveyancing_clients as sub3_client
	LEFT OUTER
	  JOIN conveyancing_quotes as sub3_quote
		ON sub3_client.id = sub3_quote.id
	WHERE sub3_quote.payment_time > 1265846400 AND sub3_quote.payment_time < 1266537599
)	AS paid
	ON paid.assigned_id = csb.id
LEFT OUTER JOIN
(
	SELECT sum(sub4_quote.payment_amount) as deposit
		 , sub4_client.assigned_id
	FROM conveyancing_clients as sub4_client
	LEFT OUTER
	  JOIN conveyancing_quotes as sub4_quote
		ON sub4_client.id = sub4_quote.id
	WHERE sub4_quote.payment_time > 1265846400 AND sub4_quote.payment_time < 1266537599
)	AS deposit
	ON deposit.assigned_id = csb.id
LEFT OUTER JOIN
(
	SELECT sum(sub5_quote.legal_fee) as case_value
		 , sub5_client.assigned_id
	FROM conveyancing_clients as sub5_client
	LEFT OUTER
	  JOIN conveyancing_quotes as sub5_quote
		ON sub5_client.id = sub5_quote.id
	WHERE sub5_quote.payment_time > 1265846400 AND sub5_quote.payment_time < 1266537599
)	AS case_value
	ON case_value.assigned_id = csb.id
 
/**
 * Status
 */
 
/* Discard */
LEFT OUTER JOIN
(
	SELECT count(discard_sub.id) as count
		 , discard_sub.assigned_id
	FROM conveyancing_clients as discard_sub
	LEFT OUTER
	  JOIN conveyancing_quotes as discard_sub_quote
		ON discard_sub.id = discard_sub_quote.id
	WHERE (discard_sub.status = 7 OR discard_sub.status = 8 OR discard_sub.status = 9) AND discard_sub.updated > 1265846400 AND discard_sub.updated < 1266537599
)	AS discard
	ON discard.assigned_id = csb.id
/* Callback */
LEFT OUTER JOIN
(
	SELECT count(callback_sub.id) as count
		 , callback_sub.assigned_id
	FROM conveyancing_clients as callback_sub
	LEFT OUTER
	  JOIN conveyancing_quotes as callback_sub_quote
		ON callback_sub.id = callback_sub_quote.id
	WHERE (callback_sub.status = 4 OR callback_sub.status = 5 OR callback_sub.status = 6) AND callback_sub.updated > 1265846400 AND callback_sub.updated < 1266537599
)	AS callback
	ON callback.assigned_id = csb.id
 
WHERE csb.level >= 30 AND csb.level <= 50
ORDER BY paid.paid DESC

The two main tables

Code MySQL:
CREATE TABLE IF NOT EXISTS `conveyancing_clients` (
	`id` int(13) unsigned auto_increment NOT NULL,
	`sale_company` smallint(3) default 0 NOT NULL,
	`campaign` smallint(5) default 0 NOT NULL,
	`assigned_id` smallint(5) unsigned default 0,
	`matter_company` smallint(3) default 0 NOT NULL,
	`stream_id` int(11) unsigned default 0 NOT NULL,
	`last_note` int(15) unsigned default 0 NOT NULL,
 
	`email` varchar(320) NOT NULL,
	`session_id` varchar(40) default '' NOT NULL,
	`password` varchar(16) NOT NULL,
	`api_key` char(16) NOT NULL,
	`prefix` varchar(5) default '' NOT NULL,
	`forename` varchar(50) NOT NULL,
	`middlename` varchar(200) default '',
	`surname` varchar(50) NOT NULL,
	`main_contact` varchar(15) default '' NOT NULL,
	`second_contact` varchar(15) default '',
	`third_contact` varchar(15) default '',
	`street` varchar(255) default '',
	`locality` varchar(50) default '',
	`region` varchar(50) default '',
	`postcode` varchar(10) default '',
 
	`status` tinyint(2) default 1 NOT NULL,
	`source` tinyint(1) default 1 NOT NULL,
	`source_extra` varchar(100) default '',
	`created` int(10) default 0 NOT NULL,
	`updated` int(10) default 0 NOT NULL,
	`active` int(10) default 0 NOT NULL,
 
	PRIMARY KEY  (`id`),
	FULLTEXT INDEX `allname` (`prefix`, `forename`, `middlename`, `surname`),
	FULLTEXT INDEX `fullname` (`forename`, `middlename`, `surname`),
	FULLTEXT INDEX `name` (`forename`, `surname`),
	FULLTEXT INDEX `residence` (`street`, `locality`, `region`, `postcode`),
	INDEX `email` (`email`),
	INDEX `api_key` (`api_key`),
	INDEX `client_api` (`email`, `api_key`),
	INDEX `client_email_login` (`email`, `password`),
	INDEX `client_id_login` (`id`, `password`),
	INDEX `sale_company` (`sale_company`),
	INDEX `matter_company` (`matter_company`),
	INDEX `campaign` (`campaign`),
	INDEX `assigned` (`assigned_id`),
	INDEX `created` (`created`),
	INDEX `updated` (`updated`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=32680 ;
 
CREATE TABLE IF NOT EXISTS `conveyancing_quotes` (
	`id` int(13) unsigned NOT NULL,
	`quote_type` tinyint(1) unsigned NOT NULL,
 
	`hidden` decimal (6, 2) unsigned default 0 NOT NULL,
	`discount` decimal(6, 2) unsigned default 0 NOT NULL,
	`speed` tinyint(1) default 1 NOT NULL,
	`legal_fee` decimal (9, 2) unsigned default 0,
	`legal_vat` decimal (9, 2) unsigned default 0,
	`disbursement_fee` decimal (9, 2) unsigned default 0,
 
	`confirm_time` int(10) unsigned default 0 NOT NULL,
	`payment_time` int(10) unsigned default 0 NOT NULL,
	`payment_contact` varchar(50) default '' NOT NULL,
	`payment_description` text,
	`payment_method` tinyint(2) unsigned default 1 NOT NULL,
	`payment_ref` varchar(20) default '' NOT NULL,
	`payment_amount` decimal(9, 2) default 0 NOT NULL,
	`payment_vat` decimal(9, 2) default 0 NOT NULL,
 
	`sale` int(10) unsigned default 0,
	`remortgage` int(10) unsigned default 0,
	`purchase` int(10) unsigned default 0,
	`hips` int(10) unsigned default 0,
	`transfer` int(10) unsigned default 0,
	PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Just any insight as to why it might be going wrong ? Should i post more data for if people want to test ?

Regards
Kieran