SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,201
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to query for this?

    Hi

    I have following two tables:
    tbl_customers

    Code:
        id  customer_name  customer_email    
    ------  -------------  ------------------
         1  Name1          email@hotmail.com 
         2  Name2          email2@hotmail.com
         3  Name3          email3@hotmail.com
    And tbl_customer_category_clicks

    Code:
        id  category_id  customer_id  clicks      
    ------  -----------  -----------  ------
         3           22            2       4
         4           23            2       22
         5           19            2       1         
         6           12            2       6
         8            5            1      10         
         9           20            1      20

    What i want?

    I want to list all the customer from tbl_customer with category_id having maximum no of clicks.
    For example, output should be:

    Code:
    category_id  customer_id  clicks      
    -----------  -----------  ------     
               20           1      20  
               23           2      22  
               0            3      0
    Thanks
    PHPycho - Magento Freelancer
    Free Modules: jQuery LightBoxes | Frontend Links Manager & more...
    Commercial Modules: Custom Login Redirect Pro | Store Restrction Pro & more...

    Follow me on Twitter @ magepsycho

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    And if a customer has more categories with the same max number of clicks?

  3. #3
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,201
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    And if a customer has more categories with the same max number of clicks?
    Then anyone would do. Not that there should be only one result for category_id per customer.

    Thanks
    PHPycho - Magento Freelancer
    Free Modules: jQuery LightBoxes | Frontend Links Manager & more...
    Commercial Modules: Custom Login Redirect Pro | Store Restrction Pro & more...

    Follow me on Twitter @ magepsycho

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Try this (didn't test it)
    Code:
    SELECT
        c.id
      , cc2.category_id
      , cc2.clicks
    FROM tbl_customers AS c
    LEFT OUTER JOIN 
      (SELECT  
           cc.id
         , cc.category_id
         , cc.clicks
       FROM tbl_customer_category_clicks AS cc
       INNER JOIN
         (SELECT 
              customer_id
            , MAX(clicks) AS maxclicks
          FROM tbl_customer_category_clicks
          GROUP BY customer_id
         ) AS mc
       ON cc.customer_id = mc.customer_id
       AND cc.clicks = mc.maxclicks
       GROUP BY cc.id
      ) AS cc2
    ON c.id = cc2.customer_id

  5. #5
    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)
    guido, your inner GROUP BY is truly evil

    nice one!!

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

  6. #6
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,201
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Guido & Rudy.
    That worked like a charm.
    PHPycho - Magento Freelancer
    Free Modules: jQuery LightBoxes | Frontend Links Manager & more...
    Commercial Modules: Custom Login Redirect Pro | Store Restrction Pro & more...

    Follow me on Twitter @ magepsycho


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
  •