how can i get an accurate count based on max date when joining 3 tables when one of the join fields is many to 1? So, I have 3 tables that I am attempting to get counts for based on a groupid, and a task code.  There are a few issues I am having as some of the relationships are many to one, which I think is somehow inflating my counts.  I will list my 3 tables with the pertinent attributes.
task_table contains:
task_code - would like to get the counts of each one in a group id, would like to use the latest instance basedon event date.
sol_id -used to join to worktable; many sol_id to one m_id is possible
edate -need to use to get one record
cur_id - where cur_id = 1 in the where clause
worktable contains:
sol_id - used to join to task_table
m_id - used to join to grouptable
grouptable contains:
m_id
groupid- used to group the task_code to get count
I'd like the end result to look like:




group_id
task_count
task




5555
45
A


5555
4
N


5624
67
A


5624
23
O


5624
42
X




I have been attempting to run a number of queries, but the counts I am getting back do not look correct.  I am concerned that it is somehow returning more than one instance of the m_id somehow?  Here is the query in question:
select  c.groupid, count(c.groupid) group_count, a.task_code from task_table a
join worktable b
on a.sol_id = b.sol_id
join grouptable c
on b.m_id= c.m_id
where   a.cur_id = 1 and a.task_code is not null 
group by c.groupid, a.task_code;

If I add 'edate = (select max(edate) from task_table)' in the where clause, it returns an empty table.
I am unsure how to incorporate edate to get only the newest record that fits the criteria in the where clause.  The reason I think I want to use this is because there could be more than one sol_id that is associated with a m_id, so i'd just like to include only the newest record with a cur_id in the count. Thank you for your time.
sample data
task_table




task_code
sol_id
edate
cur_id




A
23
6/7/09
1


A
24
6/4/09
1


A
23
6/10/09
0


B
45
6/2/09
1


B
42
6/3/09
1


C
34
10/8/10
0


C
83
9/10/09
1




work table




sol_id
m_id




23
1234


24
1234


45
1832


42
1343


83
7623




group table




m_id
group_id




1234
A76


1832
Y23


1343
A76


7623
Y23




looking at these tables, the result should look like the following




group_id
task_count
task




A76
2
A


Y23
1
C




( A76 should only count sol_id 23 and 42) ( Y23 should only count sol_id 83)
mmm based on your sample date your query is not wrong. but your expected results are wrong.
select
    a.task_code,a.sol_id,a.cur_id
    ,b.m_id
    ,c.groupid
FROM
    task_table AS a
    inner join worktable AS b on a.sol_id = b.sol_id
    inner join grouptable AS c on b.m_id= c.m_id
    where
        a.cur_id = 1 and a.task_code is not null
ORDER BY c.groupid, b.sol_id;

select
    c.groupid
    ,count(c.groupid) AS group_count
    ,a.task_code
from task_table AS a
join worktable AS b on a.sol_id = b.sol_id
join grouptable AS c on b.m_id= c.m_id
where
    a.cur_id = 1 and a.task_code is not null 
group by
    c.groupid, a.task_code
;


the group_id A76 is linked to the work table with sol_id 23,24 and 42; 23 and 24 are task_code A. 42 is task_code B.
Y23 has two m_id that connect sol_id 45 and 83 to task code A and B.
So the second output in the image is as you expect but has 2 more rows related to the task B.
Maybe the sample date is wrong because a can't see a wrong result to fix.