问题描述:

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

网友答案:
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
网友答案:
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;
相关阅读:
Top