#1
  1. A Change of Season
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    3,558
    Rep Power
    221

    How to get the number of active prospects in the account


    Hi;

    Is this the right way to get the number of prospects in an active account?

    Explain returns 4000 rows. Not sure if its supposed to be that way or not

    Code:
    SELECT COUNT(*) AS active_prospects
    FROM   launch_launch_prospect 
           INNER JOIN launch_launches 
                   ON launch_launch_prospect.launch_id = launch_launches.id 
                      AND launch_launches.launch_status = 'active' 
           INNER JOIN launch_owners 
                   ON launch_launches.user_id = launch_owners.id 
                      AND launch_owners.id = 1
    Explained

    Code:
    CREATE TABLE `launch_owners` (
     `id` int(11) NOT NULL AUTO_INCREMENT,
     `account_type` varchar(255) NOT NULL,
     `secret_key` varchar(255) NOT NULL,
     `email` varchar(255) NOT NULL,
     `password` varchar(255) NOT NULL,
     `date_added` date NOT NULL,
     `status` varchar(255) NOT NULL,
     `timezone` varchar(255) NOT NULL,
     `temp_password` int(11) DEFAULT NULL,
     `password_update_request` int(11) DEFAULT NULL,
     PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
    Code:
    CREATE TABLE `launch_launch_prospect` (
     `id` int(11) NOT NULL AUTO_INCREMENT,
     `launch_id` int(11) NOT NULL,
     `prospect_id` int(11) NOT NULL,
     `time_added` varchar(255) NOT NULL,
     `date_added` date NOT NULL,
     `source` varchar(255) NOT NULL,
     `timer_loads` int(11) DEFAULT '0',
     PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=4232 DEFAULT CHARSET=utf8
    Code:
    CREATE TABLE `launch_launches` (
     `id` int(11) NOT NULL AUTO_INCREMENT,
     `demo` int(11) NOT NULL DEFAULT '0',
     `launch_timezone` varchar(255) NOT NULL,
     `css_file` varchar(50) NOT NULL DEFAULT '1.css',
     `when_expires` varchar(255) NOT NULL DEFAULT 'do_nothing',
     `timer_digit_bg` varchar(255) NOT NULL DEFAULT '#000000',
     `timer_backdrop` varchar(255) NOT NULL DEFAULT 'transparent',
     `timer_digit_color` varchar(255) NOT NULL DEFAULT '#ffffff',
     `timer_width` varchar(55) NOT NULL DEFAULT 'smaller',
     `timer_font_family` varchar(255) NOT NULL DEFAULT 'Anton',
     `timer_opacity` varchar(255) NOT NULL DEFAULT '1',
     `timer_border_radius` varchar(255) NOT NULL DEFAULT '0',
     `timer_position` varchar(255) DEFAULT NULL,
     `launch_status` int(11) NOT NULL DEFAULT '1',
     `launch_timer_style` int(11) NOT NULL DEFAULT '1',
     `launch_timer_style_email` varchar(255) NOT NULL DEFAULT 'white',
     `launch_timer_email_font` varchar(255) NOT NULL DEFAULT 'arial',
     `title` varchar(255) NOT NULL,
     `launch_type` varchar(255) NOT NULL,
     `user_id` int(11) NOT NULL,
     `product_id` int(11) NOT NULL,
     `open_offer_x_days_or_hours_or_minutes_after_last_plc` int(11) NOT NULL DEFAULT '6',
     `evergreen_launch_days_hours_minutes` varchar(255) NOT NULL DEFAULT 'days',
     `offer_open_date` varchar(255) NOT NULL,
     `offer_open_time` varchar(255) NOT NULL,
     `offer_close_date` varchar(255) NOT NULL,
     `offer_close_time` varchar(255) NOT NULL,
     `sales_page` varchar(500) NOT NULL,
     `offer_closed_page` varchar(255) NOT NULL,
     `how_many_days_or_hours_or_minutes_keep_sales_page_open` varchar(255) NOT NULL DEFAULT '4',
     `keep_sales_page_open_days_or_hours_or_minutes` varchar(255) NOT NULL DEFAULT 'days',
     `report_mode` varchar(255) NOT NULL DEFAULT 'off',
     `timer_loads` int(11) NOT NULL DEFAULT '0',
     `thumbnail` varchar(255) NOT NULL,
     `delete_column` datetime NOT NULL,
     PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=120 DEFAULT CHARSET=utf8
  2. #2
  3. Backwards Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,914
    Rep Power
    9646
    Originally Posted by English Breakfast Tea
    Is this the right way to get the number of prospects in an active account?
    The query should be easy:
    1. Get all the required tables into the query, joining on foreign keys
    2. Apply the conditions you need (ie, what's an "active account") wherever they're needed
    3. COUNT

    Is that what you did?

    Originally Posted by English Breakfast Tea
    Explain returns 4000 rows. Not sure if its supposed to be that way or not
    EXPLAIN gives you an estimate for the number of rows. With some exceptions, MySQL doesn't really know the exact number until it actually runs the query. Which EXPLAIN does not do.
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,795
    Rep Power
    4331
    i would have written the FROM clause slightly differently
    Code:
    SELECT COUNT(*) AS active_prospects
      FROM launch_owners
    INNER
      JOIN launch_launches
        ON launch_launches.user_id = launch_owners.id 
       AND launch_launches.launch_status = 'active' 
    INNER
      JOIN launch_launch_prospect 
        ON launch_launch_prospect.launch_id = launch_launches.id 
     WHERE launch_owners.id = 1
    you're interested in the count for a specific owner, so that's the most limiting condition, so put that table first

    then, for only that owner, join to launches, but only the active ones

    then join to prospects

    the way you've written your FROM clause, it looks like you're getting all prospects, then joining to launches that are active (thereby throwing away prospects of inactive launches), then joining to owners that are owner 1 (thereby throwing away launches of other owners)

    now, to be fair, the optimizer is smart enough not to actually execute the query the way you've written it, but i always recommend that you write the FROM clause in the order that makes most sense from a logical point of view
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2016
    Location
    Cheshire, UK
    Posts
    90
    Rep Power
    72
    As the purpose of the query appears to be "count prospects for active launches belonging to owner 1", then (unless referential integrity failure means there may not be a launch_launch_launch_owner record for owner1) then I would expect a simplified version to give the same answer
    Code:
    SELECT COUNT(*) AS active_prospects
    FROM
        launch_launches L
        INNER JOIN 
        launch_launch_prospect P 
            ON P.launch_id = L.id 
    WHERE  L.user_id = 1
       AND L.launch_status = 'active'
  8. #5
  9. A Change of Season
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    3,558
    Rep Power
    221
    About Rudy's answer, from what I see, when there are INNER JOINs involved, as long as the conditions are correct, the JOINS filter out the unwanted data and there doesn't seem to be any difference in the end results which table is first.

    Perhaps that's why Rudy's query's explain is 100% like mine.

    However, Barand's a bit different. Maybe this explains it => https://snag.gy/DAzeQp.jpg
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,795
    Rep Power
    4331
    Originally Posted by English Breakfast Tea
    ... there doesn't seem to be any difference in the end results
    yes, i acknowledged that, didn't i

    but did you not see the advantage of clarity in purpose? don't you agree my sql makes more sense? if you were debugging someone else's sql (or perhaps your own, some time after you've written it), would the clarity not go a long way to help you understand what the query was supposed to be doing?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. A Change of Season
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    3,558
    Rep Power
    221
    Hey uncle Rudy;

    Originally Posted by r937
    yes, i acknowledged that, didn't i
    Yes, you did. And that's why you've been promoted to uncle.

    you're interested in the count for a specific owner
    I don't see it that way man.

    I choose the FROM table based on "what am I looking for".

    And here I am looking for "the number of prospects".

    As you mentioned you're interested in the count.

    I hope it makes sense.

    Thanks
  14. #8
  15. Wiser? Not exactly.
    Devshed God 2nd Plane (6000 - 6499 posts)

    Join Date
    May 2001
    Location
    Bonita Springs, FL
    Posts
    6,276
    Rep Power
    4193
    You're interested in the number of prospects yes, but not all the prospects. You want the number of prospects for a specific owner.

    Generally when it comes to organizing the joins in a query I find it best to follow the relationship based on whatever the condition I'm querying for is. Since your condition is where the owner id = 1, then the logic would be:
    1) Find the owner we want
    2) Find that owner's active launches
    3) Find the prospects belonging to those launches
    4) Count the results

    That's the logic order that his query format is following. Both queries give the same result, sure, but his is easier to understand/follow in my opinion.

    Comments on this post

    • r937 agrees : thanks, i'm glad someone agrees ;o)
    Recycle your old CD's



    If I helped you out, show some love with some reputation, or tip with Bitcoins to 1N645HfYf63UbcvxajLKiSKpYHAq2Zxud

IMN logo majestic logo threadwatch logo seochat tools logo