SQL query for distinct professors and courses

user_4805357 0
Viewed 46986

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

schema mysql sql
4 Answers

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;
user_95367600

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;
user_111289440

seems you need left join instead of inner join. i think you don't need distinct() with this kind of information

select distinct p.Name, c.Name, s.*
from schedules s
left join Professor p on p.id = s.professor_id
inner join Department d on d.department_id = p.department_id
left join Course c on s.course_id= c.id

but if you only want professor and course info, assuming there's some professors that doesn't have schedules yet. so no course info yet.

select distinct p.Name, c.Name
from Professor s
left join Course c on c.professor_id = p.id
user_64290720

I would use a left join here, and only involve the professor and course tables.

SELECT
    p.NAME,
    COALESCE(c.COURSE_ID, 'no courses available') AS COURSE_ID
FROM PROFESSOR p
LEFT JOIN COURSE c
    ON p.ID = c.PROFESSOR_ID
ORDER BY
    p.NAME;

The left join option lets us display a message should a given professor have no courses associated with him.

user_18632290
Related