sql - postgresql join confusion -

i'm trying make statement work. can't figure out.

4. list names of students never took course databases.

i have this:

select distinct s1.name, e1.section_id students s1     inner join enrollment e1 on e1.student_id = s1.id e1.course_id != 12 

but doesn't remove student took section, i'm stuck.

database looks (i'm soo sorry, don't know how insert database here)

table students (     id              integer primary key,     name            varchar(255),     graduation_date date,     major_id        integer references departments(id) );  insert students (id, name, graduation_date, major_id) values     (1, 'joe', null, 10); insert students (id, name, graduation_date, major_id) values     (2, 'amy', '2009-04-22', 20); insert students (id, name, graduation_date, major_id) values     (3, 'max', null, 10);   create table courses (     id              integer primary key,     title           varchar(255),     units           integer,     department_id   integer references departments(id) );  insert courses (id, title, units, department_id) values     (12, 'databases', 4, 10); insert courses (id, title, units, department_id) values     (22, 'compilers', 4, 10); insert courses (id, title, units, department_id) values     (32, 'calculus 1', 4, 20);  create table sections (     id              integer primary key,     course_id       integer not null references courses(id),     instructor_id   integer references faculty(id),     year            integer );  insert sections (id, course_id, instructor_id, year) values     (12, 12, 6, 2007); insert sections (id, course_id, instructor_id, year) values     (13, 12, 1, 2008); insert sections (id, course_id, instructor_id, year) values     (14, 22, 1, 2008); insert sections (id, course_id, instructor_id, year) values     (23, 12, 6, 2009);  create table enrollment (     id          integer primary key,     student_id  integer not null references students(id),     section_id  integer not null references sections(id),     grade_id    integer references grades(id) );  insert enrollment (id, student_id, section_id, grade_id) values     (14, 1, 12, 8); insert enrollment (id, student_id, section_id, grade_id) values     (15, 1, 13, 3); insert enrollment (id, student_id, section_id, grade_id) values     (16, 1, 14, 5); insert enrollment (id, student_id, section_id, grade_id) values     (17, 1, 32, 1); insert enrollment (id, student_id, section_id, grade_id) values     (18, 1, 34, 2); insert enrollment (id, student_id, section_id, grade_id) values     (19, 1, 53, 13); insert enrollment (id, student_id, section_id, grade_id) values     (24, 3, 12, 2); insert enrollment (id, student_id, section_id, grade_id) values     (25, 3, 14, 5); insert enrollment (id, student_id, section_id, grade_id) values     (26, 3, 32, 1); insert enrollment (id, student_id, section_id, grade_id) values     (27, 3, 34, 2); insert enrollment (id, student_id, section_id, grade_id) values     (28, 3, 54, 7); insert enrollment (id, student_id, section_id, grade_id) values     (34, 2, 43, 3); insert enrollment (id, student_id, section_id, grade_id) values 

no, don't join , try clean distinct. that's bad approach. instead think of want select first. write query step step.

the "students never took course databases"

• all students except took course databases
• all students not in set of students took course databases
• all students whom not exists databases course enrolement

i have highlighted keywords needed. have 3 options:

• write query except
• write query not in
• write query not exists

try these , come here if have further problems.

update: solved (and accepted answer :-), here ways write query:

query in clause:

select name  students  id not in  (   select student_id    enrollment    section_id in    (     select id      sections      course_id = (select id courses title = 'databases')   ) );  

query exists clause:

select name  students  not exists (   select *    enrollment    section_id in    (     select id      sections      course_id = (select id courses title = 'databases')   )   , student_id = students.id );  

query except (which not solution here, because queries students table twice, except straight-forward way problem). using join on subquery here instead of where students.id in (...), in order show technique.

select name  students  join (   select id   students   except   select student_id    enrollment    section_id in    (     select id      sections      course_id = (select id courses title = 'databases')   ) ) found_students on found_students.id = students.id;  

query count , having, looks quite compact. bit more prone errors. 1 thing not confuse on , in outer joins, count correct column. must make sure count non-nullable field of table sections, sure none of student's enrollments matched actual databases section.

select s.id, s.name students s left join enrollment e on e.student_id = s.id left join sections s on s.id = e.section_id                       , s.course_id = (select id courses title = 'databases') group s.id, s.name having count(s.id) = 0;