10

I have two tables, Courses and Faculties.

  • Courses has columns: ID(primary key), description, level, instructor_id(foreign key), and semester.
  • Faculties has columns: faculty_id(primary key), name, date_of_birth, address, email, and level.

instructor_id in Courses references faculty_id in Faculties.

I'm trying to write a query which lists all the instructors who teach more than one course. As I'm new to SQL in general I'm utterly stumped as to how to go about doing this. There are rows in the Courses table with the same value for instructor_id. Thus far I've already joined the tables like this:

SELECT "Courses".description, "Faculties".name FROM "Courses" INNER JOIN 
"Faculties" ON "Courses".instructor = "Faculties".faculty_id;

But I don't know how to filter out the rows which which duplicate values under Instructor column (in other words filter the classes with the same instructor).

Omar Kadery
  • 165
  • 1
  • 4
  • 14
  • `GROUP BY` and `HAVING` with `COUNT` – PM 77-1 Mar 15 '15 at 20:20
  • Did you create your tables with double quotes? ([I wouldn't.](http://stackoverflow.com/questions/20878932/are-postgresql-column-names-case-sensitive/20880247#20880247)) – Erwin Brandstetter Mar 15 '15 at 23:37
  • @ErwinBrandstetter I've noticed that many tools do that to make sure that names that match a command/keyword or with special characters (dash, space, ...) work. I would also avoid using them and make sure the table and field names are valid as is. – Alexis Wilke May 25 '22 at 17:07

1 Answers1

21

This is an aggregation query. If you just want the instructor id, then you can use:

select instructor_id
from courses
group by instructor_id
having count(*) > 1;

To get additional information, join in the other table.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786