Reports - select with lots of joined subqueries with counts

Databases
1

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.

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

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

2

this seems way too complex for someone just to be able to glance at it and figure out your error, especially without actual data

here’s a suggestion: run each subquery separately, and inspect the results

only you can track down the actual data

3

ahh i figured it’d be something like last time when i’d be using joins but no subqueries and multie ‘counts’ and they’d interfer with each other. With the data being returned giving me the total of all but not in the correct place of who it’s assigned to, i figured there was another mistake like before 'cause of something i didn’t know about.

I’m not sure if i have time to spent taking it apart and trying it all :S i may have to do one query to get the users and then one big query for each user and combine it in php. Performance down the drain lol ;/

4

how’s that old saying?

i don’t have time to do it right, but i have lots of time to do it over?

:cool:

5

Haha

I’m afraid my none-technical manager wouldn’t and doesn’t understand. It’s one of the least important things on my list atm anyway. I’d like to get it working though.

6

when you’re ready, run each of the subqueries separately, that should tell you where the trouble lies

:cool:

7

Also one small thing to note CASE is a reserved word, you shouldn’t use it as an alias as you have.

Even note in the code you posted above the name gets highlighted in red.

8

Aye, hence why i put it in quote marks

9

shouldn’t they be backticks??

(Genuine question - I am not sure).

bazz

10

It’s ok for an alias in the select list
http://dev.mysql.com/doc/refman/5.0/en/identifiers.html