Join Table

Hi, I'm using MS Access and ASP and I have a problem Join 2 tables

Table 1
-------
c1 | Course1
c2 | Course2
c3 | Course3
c4 | Course4

Where (c1,c2, c3...) are primary Key


Table 2
-------
c1 | Student1
c1 | Studnet2
c2 | Student1
c3 | student2
c4 | student1


What is the proper SQL to return table 3?
(First column is list of all courses, second column return only student1)

Table 3
-------
c1 | Student1
c2 | Studnet1
c3 | [NULL]
c4 | Student1

thanks

you can do this

it will display this

cid student
---- ----------
c1 Student1
c2 Student1
c4 Student1

Thank Memnoch, but I want to display all the record in table 1 (include c3), do you know how to do it? I tried left join but it did not work

SELECT Course.CourseID, qurystudent.Student
FROM Course LEFT JOIN (SELECT Student.CourseID, Student.Student
FROM Student
WHERE ((Student.Student)='Student1')) as qurystudent ON Course.CourseID = qurystudent.CourseID;


S-

Cool..... it works, Thanks sbaxter