CODEWITHSUNDEEP
sqlpostgresql
Omar Kadery
Omar Kadery

Reputation: 193

Listing rows which have the same value in a certain column in postgresql

I have two tables, Courses and Faculties.

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).

Upvotes: 13

Views: 20642

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

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.

Upvotes: 23

Related Questions