CODEWITHSUNDEEP
sqlms-access
Tariq Abdullah
Tariq Abdullah

Reputation: 75

SQL Join operation in multiple tables

I have three tables in MS Access as follow:

--Students(ID, Name, Class)
--Subjects (ID, Name)
--Marks (ID, StudentID, Subject.ID)

Relation is as follow: 

Marks.Subject = Subjects.ID
Marks.StudentID = Students.ID

Please Help me write a query that can return Name of Students and All SubjectNames and Marks of that student.

Currently I have this query but it returns marks separately.

select Students.Name, Marks.Obtained, Subjects.Name from Marks
inner join Students on Marks.StudentName = Students.ID

Upvotes: 0

Views: 519

Answers (5)

FirstLoser
FirstLoser

Reputation: 23

I would reorder this as follows:

select A1.Name as 'Student Name'
  , A3.Name as 'Subject'
  , A2.Obtained as 'Mark Obtained' 
from Students AS A1  
     inner join Marks as A2 on A1.ID = A2.StudentID
     inner join Subjects AS A3 on A2.Subject = A3.ID GO;

I hope that helps.

Upvotes: 0

Ehm
Ehm

Reputation: 113

Try this:

SELECT a.name, b.obtained, c.name
FROM studentTable a
INNER JOIN marksTable b ON a.ID = b.StudentID
INNER JOIN subjectsTable c ON b.Subject.ID = c.ID

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93754

You have joined students and marks table with that Join Subjects table too

SELECT students.NAME, 
       marks.obtained, 
       subjects.NAME 
FROM  ( marks 
       INNER JOIN students 
               ON marks.studentname = students.id )
       INNER JOIN subjects 
               ON marks.subject = subjects.id

Upvotes: 1

Dgan
Dgan

Reputation: 10295

try this:

select Students.Name, Marks.Obtained, Subjects.Name from Marks
inner join Students on Marks.Subject = Subjects.ID
Marks.StudentID = Students.ID

Upvotes: 0

Wild Eagle
Wild Eagle

Reputation: 41

This will help you : 



 select Students.Name, Marks.Obtained, Subjects.Name from Marks
    inner join Students on Marks.StudentName = Students.ID
    inner join Subjects on Marks.Subject = Subjects.ID;

Upvotes: 0

Related Questions