<div>
Are &quot;Student&quot; and &quot;Teacher&quot; a Query instead of Table?<br />
<br />
<br />
try use left join, like:<br />
<br />
<br />
SELECT A.studentName<br />
FROM Student AS A<br />
LEFT JOIN <br />
(SELECT * FROM Teacher WHERE TeacherName = 'John Doe') AS b<br />
ON A.CourseN=B.CourseN<br />
WHERE B.CourseN IS NULL
</div>


<div>
Hi, student and teacher are tables. &nbsp;Looking at your solution right now and trying them out, i'll be back in a little
</div>


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


<div>
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.<br />
<br />
The only possible relationships I see are:<br />
<br />
1. Teacher may have many students<br />
2. Teacher may have many courses<br />
<br />
There is no relationship that shows what courses a student has taken<br />
<br />
Please let's backup and work on a sound table design before you worry about that query.<br />
<br />
More to follow
</div>


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


<div>
You should have a table to link between Course and Student, like:<br />
<br />
Course:<br />
<br />
Course_ID<br />
Course_Name<br />
Teacher_ID<br />
<br />
<br />
Course_Student:<br />
<br />
Course_ID<br />
Student_ID<br />
<br />
<br />
So that you can establish the relationship between teacher, course and students.
</div>


<div>
I think you should be thinking more along these lines:<br />
<br />
Teacher = [<u>TeacherName</u>]<br />
Course = [<u>CourseN</u>]<br />
Student = [<u>StudentName</u>]<br />
Room = [<u>Room #</u>]<br />
<br />
ScheduledCourse = [<u>CourseN,Quarter,DayTime</u>,TeacherName, Room #]<br />
StudentEnrollment = [<u>studentName,Course N, Quarter, DayTime</u>]<br />
<br />
Even the above, needs improvement with better keys and perhaps other relationships, depending on your business needs.<br />
<br />
&nbsp;<br />

</div>


<div>
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. <br />
<br />
SELECT studentName<br />
FROM Student<br />
WHERE CourseN not in<br />
(SELECT CourseN<br />
FROM Teacher<br />
WHERE TeacherName = &quot;John Doe&quot;);
</div>


<div>
Gotta step away from the computer for a minute, i'll be back though<br />
<br />
but I definitely can't change how the tables work guys.. this is an assignment. &nbsp;So I have to work with how the tables are even if they're not very good unfortunately...<br />
<br />
Just trying to understand how to make this work so that I don't have to come back here asking questions for every problem =)<br />
<br />
thanks for everyone's input, i'll be back in a bit
</div>


<div>
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<br />
<br />
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..<br />
<br />
so if anyone can explain and figure this out without using JOIN, i'd be most grateful, thanks
</div>


<div>
this might work without using joins:<br />
<br />
SELECT  A.studentName, A.CourseN,  B.TeacherName<br />
FROM Student AS A, Teacher AS B<br />
WHERE B.TeacherName = 'John Doe' AND A.CourseN = B.CourseN
</div>


<div>
You are aware that we are not allowed to do homework assignments, if that's what this is. &nbsp;<br />
<br />
That aside, I must tell you there is no possible way to determine what classes a student has taken with the givens you presented. &nbsp;No way. And if someone is telling you otherwise, you have a bigger problem than a bad table design. &nbsp;<br />
<br />
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. &nbsp;Further a student cannot be associated with more than 1 teacher, so the whole thing doesn't make a lot sense. <br />
<br />
If you got a correct answer from one of the queries, it's only because you have limited test data.
</div>


<div>
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.. <br />
<br />
unfortunately those are how my tables have to be, so as crappy as they are that's what i have to use
</div>


<div>
&gt;unfortunately those are how my tables have to be<br />
<br />
Then the correct answer is: it's impossible--it's impossible to know which courses a student has taken in THOSE tables. &nbsp;Seriously.<br />
<br />
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.
</div>


<div>
<div class="content wysiwyg-content">
Tables<br />
Teacher = [<u>CourseN, Quarter</u>,TeacherName]<br />
Course = [<u>Course N Quarter , DayTime</u>, &nbsp;Room #]/ Examples of DayTime: M 2:00, <br />
W 4:50, and T 8:00.<br />
Student = [<u>studentName,Course #</u>, Quarter]<br />
<br />
^The underlined words are the primary keys<br />
<br />
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<br />
<br />
SELECT A.studentName<br />
FROM Student AS A<br />
WHERE A.CourseN EXIST<br />
(SELECT B.CourseN<br />
FROM Teacher B<br />
WHERE B.TeacherName&lt;&gt;&quot;John Doe&quot; AND A.CourseN=B.CourseN);<br />
<br />
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? &nbsp;Thanks
</div>
</div>


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

Help with &quot;IN&quot; and &quot;NOT IN&quot; (MS Access)

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

Microsoft Access

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.

Help with &quot;IN&quot; and &quot;NOT IN&quot; (MS Access)

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