Complex connection request

I originally did this with four separate requests - I think I need to optimize my code with connections ...

I have four tables (as shown below):

student_id (int 11)
course_id (int 11)

project_id (int 11)
course_id (int 11)

project_id (int 11) - Primary Key -
professor_id (int 11)
project_name (varchar 50)

professor_id (int 11) - Primary Key -
professor_name (varchar 50)
project_email (varchar 50)

What information do I need?

I need all data from sl_project

and sl_professor

, where sl_student_course.course_id

= sl_project_course.course_id

And then I need to use project_id


SO ...




, sl_project.project_name

, → sl_professor.professor_name


Does this make sense?

  • If not, here's another example!
course_id | 1

project_id | 1
course_id | 1

project_id | 1
professor_id | 2
project_name | project1

professor_id | 2
professor_name | John Doe
professor_email |


source to share

2 answers

I hope I understood correctly your relationship with the tables in which the professors joined the projects (in the sl_project table).

This will give you project and professor details (using TSQL):

SELECT P.project_name, F.professor_name, F.professor_email
FROM sl_project P
    INNER JOIN sl_professor F ON F.professor_id = P.professor_id

    -- Not sure where course is coming into play, since you aren't selecting
    -- anything from the students/course table, but if you need to ensure there is a
    -- course for this project and students in the course, these joins are necessary.
    INNER JOIN sl_project_course C ON C.project_id = P.project_id
    INNER JOIN sl_student_course S ON S.course_id = C.course_id
WHERE P.project_id = @project_id




Try something along these lines:

 select sl_project.project_name, sl_professor.professor_name, sl_professor.professor_email 
 from sl_student_course
 natural join sl_project_course
 natural join sl_project
 natural join sl_professor


Natural join is good because it prefers conditional configuration - so if you plan your db project as expected, you can work safely.

Update: replaced * with certain requested fields.



All Articles