Join query with multiple tables invovled

Question

Hi, I am using Join in sql for first time with respect to many tables, I have got error with this :

I have three tables,

Semester table

StudentID Department Semester
1          1           1

Course Table

CourseID CourseName Semester 
1          S.E         1
2          D.B         1

ExamAttend Table(foreign keys as StudentID and CourseID)

StudentID CourseID Marks
1           1        88
1           2        90

I am trying to get the reslut through

Select CourseName,Marks 
from CourseID 
Inner Join ExamAttend on (
    Select CourseID from Course as c, Semester as s where s.Semester = c.Semester 
) = ExamAttend.CourseID;

This query is Showing me error that subquery cannot return multiple query when used with '='

Anyone can suggest me a way to get the query done? Am i missing any syntax with inner join?

Answer 1

Try joining with each table separately:

SELECT
    CourseName,
    Marks
FROM Course
INNER JOIN Semester
ON Semester.StudentID = ExamAttend.StudentID
INNER JOIN ExamAttend
ON CourseID.CourseID = ExamAttend.CourseID
AND ExamAttend.SemesterId = Semester.SemesterId

Answer 2

SELECT CourseName,Marks 
FROM Course C INNER JOIN ExamAttend E
ON C.CourseID = E.CourseID 
INNER JOIN Semester S
ON S.StudentID = E.StudentID 

Answer 3

Try this

SELECT     Semester.StudentID, Course.CourseID.CourseName, ExamAttend.Marks
FROM         ExamAttend INNER JOIN
                      Semester ON ExamAttend.StudentID = Semester.StudentID INNER JOIN
                      Course ON ExamAttend.CourseID = Course.CourseID

Answer 4

When joining tables the best thing you can do is: write it on paper first and use math collection algebra. Like if you have three tables: Student, Course, ExamAttend , you can write this like: (Student AND (Course AND ExamAttend)) Using this the AND can be replaced by INNER JOIN, writing: (Student Inner Join (Course Inner Join ExamAttend)).

Because you have to set the distincted columns, this would resolve into: Student Inner Join (Course Inner Join ExamAttend On Course.CourseID = ExamAttend.CourseID) On Student.StudentID = ExamAttend.StudentID

The Sql parser would first build a projection of Course and ExamAttend and second the projection with Student.

If you want a projection with all students even the students don't have a grade for a course or whatever, you can write in your collection algebra: Student OR (Course AND ExamAttend), in which OR can be LEFT OUTER join.

Be aware that you pick the right table in which you can follow a path to the tables you would like to join. There are several solutions and picking the wrong one can result in a slow query.

Hope it helps.