the two table
first table
studentmessage
id departmentid
1 1
2 1
3 1
4 1
5 2
6 2
7 3
8 4

second table departmentmessage
departmentid departmentname
1 cs
2 math
3 english
4 china
5 science

i want give a table
as
departmentid departmentname personnumber
1 cs 4
2 math 2
3 english 1
4 china 1
null science 0


and
i want give a table than personnumber is max
as
departmentid departmentname personnumber
1 cs 4

how to solve the problem , who help me .
thanks in advances


i am a novice, i am a chinese, i hope everyone can help me in here.

i can use join or left join or right join to solve the problew, who give me the second way , wo want to subquery

Didn't I answer this once by private email for you a couple of weeks ago????

--Jeff Moden