Sql-server – 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

database-designoraclesql server

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)

Best Answer

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
;

enter image description here

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.

Related Question