SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Enthusiast
    Join Date
    Mar 2007
    Posts
    83
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Reports - select with lots of joined subqueries with counts

    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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Mar 2007
    Posts
    83
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by WakeMeWithAKiss View Post
    I'm not sure if i have time to spent taking it apart and trying it all
    how's that old saying?

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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Mar 2007
    Posts
    83
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by WakeMeWithAKiss View Post
    I'd like to get it working though.
    when you're ready, run each of the subqueries separately, that should tell you where the trouble lies

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #8
    SitePoint Enthusiast
    Join Date
    Mar 2007
    Posts
    83
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Aye, hence why i put it in quote marks

  9. #9
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    shouldn't they be backticks??

    (Genuine question - I am not sure).

    bazz

  10. #10
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It's ok for an alias in the select list
    http://dev.mysql.com/doc/refman/5.0/en/identifiers.html


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •