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):
[sl_student_course] student_id (int 11) ------------------ course_id (int 11) ------------------ [sl_project_course] project_id (int 11) ------------------ course_id (int 11) ------------------ [sl_project] project_id (int 11) - Primary Key - _____________________ professor_id (int 11) --------------------- project_name (varchar 50) [sl_professor] 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
fromsl_project_course
SO ...
sl_student_course.course_id
→ sl_project_course.project_id
→ sl_project.professor_id
, sl_project.project_name
, → sl_professor.professor_name
,sl_professor.professor_name
Does this make sense?
- If not, here's another example!
sl_student_course course_id | 1 sl_project_course project_id | 1 course_id | 1 sl_project project_id | 1 professor_id | 2 project_name | project1 sl_professor professor_id | 2 professor_name | John Doe professor_email | John@Doe.com
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
source to share
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.
source to share