Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
vgunas1
Starting Member
18 Posts |
 Posted - 2004-04-10 : 14:07:38
|
| Hi,I have to write a query that can display records that are not present in one table. For eg. I have a course tablle, a student table and a registered course table. The course table has the list of all courses. The student table has the list of all students. The registered course table holds the student ids and the course ids corresponding to the courses registered by the particular student. I would like to write a query that can display all the courses that havent been registered by a particular student. Can anyone help me with this. Course Tableid course name instructor1 Database design David Mok2 E-Commerce Anne Bradley3 Networks Roberta StanleyStudent Tableid student name status101 Kerri Kenney senior201 Anderson Mason freshman311 Calvin Stenison seniorRegistrationid student id course id1001 201 31002 201 11003 101 21003 101 11004 311 3I would like to display all the courses that have not been registered by student id 311(which would be course id 1 and 2).Can someone help me with this?Thanks in advance!!! |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2004-04-15 : 18:55:16
|
| A fancy LEFT OUTER JOIN, checking for NULLs in the Right-hand table should do the trick. Something like:SELECT coursenameFROM Course CLEFT JOIN Registration R on C.id = R.CourseID AND R.StudentID = 311WHERE R.CourseID IS NULLP.S. I hope you don't really have spaces in the table name or column names like your example shows. While Access allows this, it is generally considered bad practice. And it requires you to enclose everything in square brackets like [course name].--------------------------------------------------------------Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url] |
 |
|
|
|
|
|
Access Queries