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

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

    group_concat to count the number of subs?


    Hi;

    About a decade ago Rudy tried to teach me this. He then gave up and questioned human intelligence.

    I need help to build this query to avoid sql in the loop.

    How many plcs does a launch have?

    Code:
    CREATE TABLE `launch_launches` (
     `id` int(11) NOT NULL AUTO_INCREMENT,
     `title` varchar(255) NOT NULL,
     `user_id` int(11) NOT NULL,
     `product_id` int(11) NOT NULL,
     PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
    Code:
    CREATE TABLE `launch_plcs` (
     `id` int(11) NOT NULL AUTO_INCREMENT,
     `launch_id` int(11) NOT NULL
     PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8
    So far I have this which is useless. I just post it to feel better:
    PHP Code:
    $query_launches $this->db->get_where('launch_launches', array('user_id' => $_SESSION['user_id']));
    $this->data['launches'] = $query->result_array(); 
  2. #2
  3. Impoverished Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,817
    Rep Power
    9646
    Code:
    SELECT COUNT(1) FROM launch_plcs WHERE launch_id = whatever
  4. #3
  5. A Change of Season
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    3,545
    Rep Power
    221
    Originally Posted by requinix
    Code:
    SELECT COUNT(1) FROM launch_plcs WHERE launch_id = whatever
    This is good solution for someone who has a hard time learning grour_concat (me).

    However, I wanna see how it's done with 1 query. Basically, join your query to

    Code:
    SELECT FIELDS FROM launch_launches WHERE user_id = 2 AND GROUP CONCAT BUSINESS
  6. #4
  7. Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2006
    Posts
    2,674
    Rep Power
    1841
    That's not how you use the group_concat function! Please RTFM!
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  8. #5
  9. Wiser? Not exactly.
    Devshed God 2nd Plane (6000 - 6499 posts)

    Join Date
    May 2001
    Location
    Bonita Springs, FL
    Posts
    6,270
    Rep Power
    4193
    It doesn't sound like GROUP_CONCAT is what you want at all. If you wanted one query to return counts for every launch, you'd just do a simple GROUP BY and COUNT().

    Code:
    SELECT 
        l.title
        , COUNT(p.id)
    FROM launch_launches l
    LEFT JOIN launch_plcs p ON p.launch_id=l.id
    GROUP BY
        l.title
    GROUP_CONCAT is for creating something like a single comma-separated list from multiple rows of values.
    Recycle your old CD's



    If I helped you out, show some love with some reputation, or tip with Bitcoins to 1N645HfYf63UbcvxajLKiSKpYHAq2Zxud
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,793
    Rep Power
    4331
    Originally Posted by English Breakfast Tea
    How many plcs does a launch have?
    definitely not a job for GROUP_CONCAT

    and you don't need a join, if it's just a count, and you know which launch you want the count for
    Code:
    SELECT COUNT(*) 
      FROM launch_plcs
     WHERE launch_id = 937  /* id of launch that you want plcs count for */
    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,545
    Rep Power
    221
    Hello;

    What Kicken wrote is correct results.

    Rudy I need to generate a list of launch_launches as well. For some reason, I misled Requnix and Rudy the same way.

    Just trying to avoid the sql in loop.

    Here is the full scoop
    Code:
    SELECT launch_launches.title, 
           launch_launches.id, 
           launch_products.title AS product_name, 
           launch_type, 
           Count(p.id) 
    FROM   launch_launches 
           LEFT JOIN launch_plcs p 
                  ON p.launch_id = launch_launches.id 
           LEFT OUTER JOIN launch_products 
                        ON launch_launches.product_id = launch_products.id 
    WHERE  launch_launches.user_id = 1 
           AND launch_status <> 0 
    GROUP  BY launch_launches.title 
    ORDER  BY launch_launches.id
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,793
    Rep Power
    4331
    how many launch plcs does each launch launch have? exactly one? maybe one? possibly more than one?

    how many launch products does each launch launch have? exactly one? maybe one? possibly more than one?

    the reason i ask these questions is to understand why you have LEFT joins
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  16. #9
  17. A Change of Season
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    3,545
    Rep Power
    221
    Originally Posted by r937
    how many launch plcs does each launch launch have? exactly one? maybe one? possibly more than one?

    how many launch products does each launch launch have? exactly one? maybe one? possibly more than one?

    the reason i ask these questions is to understand why you have LEFT joins
    Each launch_launch has a minimum of 1 launch_plcs
    Each launch_launch can have up to 16 launch_plcs (usually 3)
  18. #10
  19. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,793
    Rep Power
    4331
    and products?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  20. #11
  21. A Change of Season
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    3,545
    Rep Power
    221
    Each product can have multiple launches
  22. #12
  23. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,793
    Rep Power
    4331
    so for "how many launch products does each launch launch have? exactly one?" you're saying exactly one?

    okay both LEFT joins should be INNER... but what i was worried about (cross join effects) is not a problem
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  24. #13
  25. A Change of Season
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    3,545
    Rep Power
    221
    Originally Posted by r937
    so for "how many launch products does each launch launch have? exactly one?" you're saying exactly one?

    okay both LEFT joins should be INNER... but what I was worried about (cross join effects) is not a problem
    I don't understand why I should change this query:

    Code:
    SELECT launch_launches.title, 
           launch_launches.id, 
           launch_products.title AS product_name, 
           launch_type, 
           Count(p.id) 
    FROM   launch_launches 
           LEFT JOIN launch_plcs p 
                  ON p.launch_id = launch_launches.id 
           LEFT OUTER JOIN launch_products 
                        ON launch_launches.product_id = launch_products.id 
    WHERE  launch_launches.user_id = 1 
           AND launch_status <> 0 
    GROUP  BY launch_launches.title 
    ORDER  BY launch_launches.id
    The results look good.


    Each launch_products can have many launch_launches.

    Each launch_launches belongs to only 1 launch_product.


    Let's say RudyLimeback decides to do a product launch and sell more of SimplySQL to make another million.

    He does a lunch in September, one in October and one In December.

    Does it make sense?

    I hope it's clear.

    Also every time I have a Corona I think of your name. It's the Lime.

    Thanks Rudy
    Last edited by English Breakfast Tea; April 1st, 2018 at 02:22 PM.
  26. #14
  27. Wiser? Not exactly.
    Devshed God 2nd Plane (6000 - 6499 posts)

    Join Date
    May 2001
    Location
    Bonita Springs, FL
    Posts
    6,270
    Rep Power
    4193
    INNER JOIN is more efficient than LEFT JOIN, but comes with the condition that if there are no matches then nothing at all is returned.

    If you know for certain there will always be at least one match for your join condition then you should use INNER JOIN.

    If you need to be able to details from launch_launches even if there are not matching rows in your other tables, then you need a LEFT JOIN.
    Recycle your old CD's



    If I helped you out, show some love with some reputation, or tip with Bitcoins to 1N645HfYf63UbcvxajLKiSKpYHAq2Zxud
  28. #15
  29. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,793
    Rep Power
    4331
    Originally Posted by English Breakfast Tea
    I don't understand why I should change this query:
    you don't ~have~ to

    Originally Posted by English Breakfast Tea
    The results look good
    you get the same results with INNER joins

    in other words, you're asking for results where the right table in a left outer join might have some unmatched rows (the whole purpose of an outer join), and yet there will never be any unmatched rows

    write the right join for the circumstances, is my advice

    get used to thinking critically about joins, don't just write one and be happy that it happens to produce results that look good, because some day you'll write a query and the results won't look so good, and you'll be wondering how come
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo