Help with "IN" and "NOT IN" (MS Access)

Are "Student" and "Teacher" a Query instead of Table?


try use left join, like:


SELECT A.studentName
FROM Student AS A
LEFT JOIN
(SELECT * FROM Teacher WHERE TeacherName = 'John Doe') AS b
ON A.CourseN=B.CourseN
WHERE B.CourseN IS NULL

Hi, student and teacher are tables.  Looking at your solution right now and trying them out, i'll be back in a little

mmm it's not working on my end as it's definitely listing students that have taken a class with John Doe

To be frank: you have a terrible, terrible table design which will give you endless grief and that's assuming you can get it to work at all.

The only possible relationships I see are:

1. Teacher may have many students
2. Teacher may have many courses

There is no relationship that shows what courses a student has taken

Please let's backup and work on a sound table design before you worry about that query.

More to follow

lol, actually that was the tables that are required to be used by this assignment.. so I can't change the tables actually =[

You should have a table to link between Course and Student, like:

Course:

Course_ID
Course_Name
Teacher_ID


Course_Student:

Course_ID
Student_ID


So that you can establish the relationship between teacher, course and students.

I think you should be thinking more along these lines:

Teacher = [TeacherName]
Course = [CourseN]
Student = [StudentName]
Room = [Room #]

ScheduledCourse = [CourseN,Quarter,DayTime,TeacherName, Room #]
StudentEnrollment = [studentName,Course N, Quarter, DayTime]

Even the above, needs improvement with better keys and perhaps other relationships, depending on your business needs.

 

Without really understanding the table structure lll just change your query to what I think you were trying to do. Exists is used when you need to check multiple columns, you are just looking up one so you should be using in. Also your double negatives don't make sense.

SELECT studentName
FROM Student
WHERE CourseN not in
(SELECT CourseN
FROM Teacher
WHERE TeacherName = "John Doe");

Gotta step away from the computer for a minute, i'll be back though

but I definitely can't change how the tables work guys.. this is an assignment.  So I have to work with how the tables are even if they're not very good unfortunately...

Just trying to understand how to make this work so that I don't have to come back here asking questions for every problem =)

thanks for everyone's input, i'll be back in a bit

OP_Zaharin, that solution does work, but I forgot to mention that my instructor doesn't want us to use JOIN.. i'll give you your points

aarontomosky: yea that's what I'm lookin for, but that doesn't work neither and is coming up with the same results as the one i wrote on my own..

so if anyone can explain and figure this out without using JOIN, i'd be most grateful, thanks

this might work without using joins:

SELECT  A.studentName, A.CourseN,  B.TeacherName
FROM Student AS A, Teacher AS B
WHERE B.TeacherName = 'John Doe' AND A.CourseN = B.CourseN

You are aware that we are not allowed to do homework assignments, if that's what this is.  

That aside, I must tell you there is no possible way to determine what classes a student has taken with the givens you presented.  No way. And if someone is telling you otherwise, you have a bigger problem than a bad table design.  

Now I can tell you that there is some relationship between teacher and student but it does not involve what you've called a course.  Further a student cannot be associated with more than 1 teacher, so the whole thing doesn't make a lot sense.

If you got a correct answer from one of the queries, it's only because you have limited test data.

dgmg yea i'm aware that you're not allowed to do homework assignments.. but i did present what i thought was a good answer and though an answer is appreciated, i DO want an explanation on why it's incorrect..

unfortunately those are how my tables have to be, so as crappy as they are that's what i have to use

>unfortunately those are how my tables have to be

Then the correct answer is: it's impossible--it's impossible to know which courses a student has taken in THOSE tables.  Seriously.

Also, I'm befuddled by the answer you chose as correct. That query returns students having something (not sure what) in common with John Doe.