I have a table1 in mySQL with the following recordset :
+--------+--------+---------+
| siteno | deptid | agentid |
+--------+--------+---------+
| 0 | 27 | 35 |
| 0 | 25 | 30 |
| 0 | 19 | 30 |
| 0 | 19 | 26 |
| 0 | 20 | 26 |
| 0 | 19 | 27 |
+--------+--------+---------+
Now I want to retrieve the distinct agentid's belonging to deptid 19
along with their respective counts of how many departments do they
belong to. Once again I repeat, I want to retrieve the agentids and
their count of departments where the minimum condition to be satisfied
is that agent must at least belong to deptid 19. Thus the resultset
which I want to generate must look like this :
+--------+---------+
| count | agentid |
+--------+---------+
| 2 | 30 |
| 2 | 26 |
| 1 | 27 |
+--------+---------+
PS : The agent no 25 has not come because he does not hv any association
with deptid 19.
Can anybody pls give me the SQL query to generate the above resultset ?
Regards
Harsh
----------------------------------------
http://mail.indiainfo.com
First you had 10MB of free mail space.
Now you can send mails in your own language !!!
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
