Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.
Moderator: General Moderators
johnc71
Forum Newbie
Posts: 10 Joined: Tue Jun 24, 2008 2:19 am
Post
by johnc71 » Fri Apr 24, 2009 3:49 am
I have three tables (see attached image) I would like to get the names of all students who are not enrolled in English class. When I ran the query:
Code: Select all
SELECT s.name FROM students s LEFT JOIN enrollment e ON s.student_id = e.student_id WHERE e.class_id != 2
I get:
Mary
Terry
John
Mark
Terry (student id 1) should not be listed. Also, Stephanie, James, and Irena should be listed.
I guess the type of JOIN I am using is not correct?
Attachments
sql_problem.gif (4.16 KiB) Viewed 962 times
susrisha
Forum Contributor
Posts: 439 Joined: Thu Aug 07, 2008 11:43 pm
Location: Hyderabad India
Post
by susrisha » Fri Apr 24, 2009 4:22 am
Code: Select all
Select students.name from students,enrollment Where enrollment.class_id!=2;
try this
viraj
Forum Newbie
Posts: 11 Joined: Wed Nov 19, 2008 7:52 am
Location: Colombo - Sri Lanka
Post
by viraj » Fri Apr 24, 2009 4:30 am
actually johnc71 your code is ok.
Terry (student id 1) is in the out put because he also enrolled in Chemistry class. ( check enrollment id = 3 )
johnc71
Forum Newbie
Posts: 10 Joined: Tue Jun 24, 2008 2:19 am
Post
by johnc71 » Fri Apr 24, 2009 4:53 am
viraj wrote: actually johnc71 your code is ok.
Terry (student id 1) is in the out put because he also enrolled in Chemistry class. ( check enrollment id = 3 )
The code is doing what it needs to do. However, the results are not right for me. so, I guess I need a new code.
I need code to, on the following question, "Give me the names of ALL students in table students, who are not enrolled in history" will give me the following results.
John
Mary
Mark
Stephanie
James
Irena
viraj
Forum Newbie
Posts: 11 Joined: Wed Nov 19, 2008 7:52 am
Location: Colombo - Sri Lanka
Post
by viraj » Fri Apr 24, 2009 6:07 am
This will work.
Code: Select all
SELECT s.name FROM students s WHERE s.name NOT IN (SELECT s.name FROM enrollment e WHERE s.student_id = e.student_id AND e.class_id = 3)
3 - history
John is the only student doing history so his record will not be in the output
Code: Select all
SELECT s.name FROM students s WHERE s.name NOT IN (SELECT s.name FROM enrollment e WHERE s.student_id = e.student_id AND e.class_id = 2)
2 - English
Only Terry is doing English and his record will not be displayed.
There may be a left join version for this.
johnc71
Forum Newbie
Posts: 10 Joined: Tue Jun 24, 2008 2:19 am
Post
by johnc71 » Fri Apr 24, 2009 1:26 pm
The code works but can you please explain this part of the code. Enrollment table does not have names so how does this code works?
John Cartwright
Site Admin
Posts: 11470 Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:
Post
by John Cartwright » Fri Apr 24, 2009 2:29 pm
johnc71 wrote: The code works but can you please explain this part of the code. Enrollment table does not have names so how does this code works?
You would still need to INNER JOIN the students table by their student id.