SQL query for distinct professors and courses

Question

The question is stated as there is a database on professors, departments, courses and schedules. Need to write a query that returns the names of all the professors and their respective courses. Each row must contain the name of the professor followed by the name of the course that the professor teaches. There must be no duplicates in terms of the rows.

The schema I have, in terms of the table name and the fields:

PROFESSOR: ID, NAME, DEPARTMENT_ID, SALARY
DEPARTMENT: ID, NAME
COURSE: ID, NAME, DEPARTMENT_ID, CREDITS
SCHEDULE: PROFESSOR_ID, COURSE_ID, SEMESTER, YEAR

The code I have right now:

SELECT DISTINCT p.Name AND c.NAME
FROM Prodessor p, Course c, Schedule S

WHERE
    p.DEPARTMENT_ID = c.DEPARTMENT_ID
    AND
    p.ID = s.PROFESSOR_ID
    AND
    c.ID = c.COURSE_ID

The result I get is a list of all professors, but there aren't multiple courses, just a single one. What's going wrong here? It also mentioned that PROFESSOR.ID is foreign to COURSE.PROFESSOR_ID, so p.ID = s.PROFESSOR_ID is valid

Answer 1

I think the below code will solve your requirement

SELECT distinct p.name,c.name
from Professor p 
inner join Schedule s on p.id=s.professor_id
inner join course c on c.id=s.course_id;

Answer 2

Alternatively if we want professors with the names of the courses they teach (or have taught) outside of their department.

select distinct p.name, c.name
FROM professor p
JOIN schedule s on s.professor_id = p.id
JOIN course c on s.course_id = c.id
WHERE c.department_id <> p.department_id;