How to query for this?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
PHPycho
Forum Contributor
Posts: 336
Joined: Fri Jan 06, 2006 12:37 pm

How to query for this?

Post 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
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: How to query for this?

Post 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
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
darek
Forum Newbie
Posts: 8
Joined: Tue Apr 19, 2011 2:26 am
Location: Opole, Poland

Re: How to query for this?

Post 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.
Post Reply