CODEWITHSUNDEEP
sqljoin
user725913
user725913

Reputation:

Complicated join query

I was originally doing this through four separate queries - I think it's time I optimize my code through the use of joins...

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 the project_id from sl_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 any sense?

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 | [email protected]

Upvotes: 3

Views: 268

Answers (2)

j13r
j13r

Reputation: 2671

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

The natural join is nice because it prefers convention over configuration -- so if you do your db design in the expected way, you safe effort.

Update: replaced * by specific fields requested.

Upvotes: 1

Bort
Bort

Reputation: 7618

Hoping I understood your table relationships correctly, which has professors joined to projects (in the sl_project table).

This will get you project and professor data (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

Upvotes: 4

Related Questions