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:
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
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;