nocturn4l
asked on
Help with "IN" and "NOT IN" (MS Access)
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
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
ASKER
Hi, student and teacher are tables. Looking at your solution right now and trying them out, i'll be back in a little
ASKER
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
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
ASKER
lol, actually that was the tables that are required to be used by this assignment.. so I can't change the tables actually =[
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
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");
SELECT studentName
FROM Student
WHERE CourseN not in
(SELECT CourseN
FROM Teacher
WHERE TeacherName = "John Doe");
ASKER
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
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
ASKER
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
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
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.
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.
ASKER
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, 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.
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.
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