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

nocturn4l
nocturn4l used Ask the Experts™
on
Tables
Teacher = [CourseN, Quarter,TeacherName]
Course = [Course N Quarter , DayTime,  Room #]/ Examples of DayTime: M 2:00,
W 4:50, and T 8:00.
Student = [studentName,Course #, Quarter]

^The underlined words are the primary keys

I'm trying to list the names of all the students who haven't taken a class taught by a teacher name.. say John Doe.. so I have

SELECT A.studentName
FROM Student AS A
WHERE A.CourseN EXIST
(SELECT B.CourseN
FROM Teacher B
WHERE B.TeacherName<>"John Doe" AND A.CourseN=B.CourseN);

to me it makes perfecct sense... but obviously it's not because it's coming out with not what i'm looking for.. can someone explain why this is wrong and the correct way of doing this?  Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ryan ChongSoftware Team Lead

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

Author

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

Author

Commented:
mmm it's not working on my end as it's definitely listing students that have taken a class with John Doe
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

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

Author

Commented:
lol, actually that was the tables that are required to be used by this assignment.. so I can't change the tables actually =[
Top Expert 2011
Commented:
- try this:

SELECT  A.studentName, A.CourseN,  B.TeacherName
FROM Student AS A
INNER JOIN Teacher AS B ON A.CourseN = B.CourseN
WHERE B.TeacherName = 'John Doe'
Ryan ChongSoftware Team Lead

Commented:
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.

Commented:
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.

 
Aaron TomoskyDirector of Solutions Consulting

Commented:
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");

Author

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

Author

Commented:
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
Top Expert 2011

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

Commented:
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.

Author

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

Commented:
>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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial