Page 1 of 1

How to query for this?

Posted: Tue Apr 19, 2011 3:37 am
by PHPycho
Hi

I have following two tables:
tbl_customers

Code: Select all

    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: Select all

    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: Select all

category_id  customer_id  clicks      
-----------  -----------  ------     
           20           1      20  
           23           2      22  
           0            3      0   
Thanks

Re: How to query for this?

Posted: Tue Apr 19, 2011 12:37 pm
by AbraCadaver
Maybe something like this (not tested):

Code: Select all

SELECT t2.category_id, t2.customer_id, MAX(t2.clicks)
FROM tbl_customers t1, tbl_customer_category_clicks t2
WHERE t1.id = t2.customer_id
GROUP BY t2.customer_id
ORDER BY t2.customer_id

Re: How to query for this?

Posted: Wed Apr 20, 2011 5:04 am
by darek
LEFT JOIN is the way here, see documentation: http://dev.mysql.com/doc/refman/5.0/en/join.html

I could give you exact solution, but you wouldn't learn anything.