MAX Date for each worker

Question

Consider two tables. Worker table has fields like W_ID, W_Name. Course table has fields like C_ID, C_Name, C_Date.

A Worker can take many courses and a course can be taken by many Workers. Therefore, there is a many to many relationship between the two tables. Break the many to many, and create a new table called Takes which consists of the foreign keys W_ID and C_ID.

Here is the question: I want to find out a list of all workers and their last course taken. For example if W_ID=1 took C_Date 20/01/2010 and C_Date 25/10/2010 and C_Date 20/12/2010.

Then the Result required is:

W_ID     C_Date
1        20/12/2010
2
3
4

Edit

I want to find the maximum date of the course each worker has done as workers would have done many courses. I am a business student, so I can't use INNER JOIN command. IN can be used instead?

I am using my SQL in Microsoft Access

Do you want just the Date of the las course taken?, not the course Id? — Lamak, Apr 28 '11 at 14:21

Quassnoi · Answer 1 · 2011-04-28T14:34:23.187

3

SELECT  w.w_id, MAX(c.c_date)
FROM    worker w
LEFT JOIN
        worker_course wc
ON      wc.w_id = w.w_id
LEFT JOIN
        course с
ON      c.c_id = wc.c_id
GROUP BY
        w.w_id

or this:

SELECT  w.w_id, MAX(c.c_date)
FROM    course с
JOIN    worker_course wc
ON      wc.c_id = c.c_id
RIGHT JOIN
        worker w
ON      w.w_id = wc.w_id
GROUP BY
        w.w_id

edited Apr 28 '11 at 14:34

answered Apr 28 '11 at 14:21

Quassnoi

413,100
91
616
614

You can use an inner join between Takes (Worker_Course) and Course; you need the outer join between Worker and Takes. – Jonathan Leffler Apr 28 '11 at 14:24
1

@Jonathan: this requires support for `SELECT t1 LEFT JOIN (t2 JOIN t3)`. Not all `RDBMS` support that. – Quassnoi Apr 28 '11 at 14:26
@Jonathan: however, it can be rewritten as a `RIGHT JOIN`, so thanks for a nice point. – Quassnoi Apr 28 '11 at 14:34

score 0 · Answer 2 · answered Apr 28 '11 at 14:23

0

select W_ID,C_ID,max(C_Date)
from(select W_ID,C_ID,C_Date
    from worker
    join takes using (W_ID)
    join course using (C_ID)
)wc
group by W_ID,C_ID;

answered Apr 28 '11 at 14:23

The op wants the date of the last course taken, this way he gets a list of all the courses taken by a worker. – Lamak Apr 28 '11 at 14:27
This does not provide the rows for workers who've not taken any courses. – Jonathan Leffler Apr 28 '11 at 14:43

MAX Date for each worker

2 Answers2

Linked