SQL Join operation in multiple tables

Question

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

Pரதீப் · Accepted Answer · 2014-12-13T12:45:33.413

1

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

edited Dec 13 '14 at 12:45

answered Dec 13 '14 at 12:18

Pரதீப்

91,748
19
131
172

It says "Syntax Error(Missing Operator) in Query expression "Marks.StudentName = Students.ID JOIN Subjects ON Marks.Subject = Subjects.I". – Tariq Abdullah Dec 13 '14 at 12:41
@TariqAbdullah - Check now. looks like Ms access does support `join` it should be `Inner Join` – Pரதீப் Dec 13 '14 at 12:42
@TariqAbdullah - Updated check now. My bad am not good at Ms access syntax – Pரதீப் Dec 13 '14 at 12:46
@TariqAbdullah - Looks like Access requires parentheses in the FROM clause for queries which include more than one join. – Pரதீப் Dec 13 '14 at 12:49
this: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) does not work – Tariq Abdullah Dec 13 '14 at 12:52
@TariqAbdullah it is not same as my answer. Did you try my updated answer – Pரதீப் Dec 13 '14 at 12:58
Thanks NoDisplayName. It works but It still returns subject name in addition to what I already have .. How can it be grouped by Name showing name of subject as column and Marks its as value – Tariq Abdullah Dec 13 '14 at 13:21
@TariqAbdullah - That is another question dude. you have to ask a new question – Pரதீப் Dec 13 '14 at 13:29
Thank you for your help. I am highly obliged. Thanks again. – Tariq Abdullah Dec 13 '14 at 13:30
BTW, Can you help me with this? – Tariq Abdullah Dec 13 '14 at 13:31
@TariqAbdullah Sorry man am not good in MS ACCESS. I am a `SQL SERVER` guy since this a basic question i just tried – Pரதீப் Dec 13 '14 at 13:38
@TariqAbdullah - check this http://stackoverflow.com/questions/16546305/pivoting-data-in-ms-access and http://stackoverflow.com/questions/16691853/transform-and-pivot-in-access-2013-sql – Pரதீப் Dec 13 '14 at 13:41

Dgan · Answer 2 · 2014-12-13T12:23:21.197

0

try this:

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

edited Dec 13 '14 at 12:23

answered Dec 13 '14 at 12:17

Dgan

10,077
1
29
51

What is `Subjects` in this query? – Rhumborl Dec 13 '14 at 12:18

score 0 · Answer 3 · answered Dec 13 '14 at 12:23

0

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;

answered Dec 13 '14 at 12:23

Wild Eagle

41
1
4

score 0 · Answer 4 · answered Dec 13 '14 at 13:01

0

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

answered Dec 13 '14 at 13:01

Ehm

113
1
9

score 0 · Answer 5 · answered Dec 13 '14 at 13:03

0

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.

answered Dec 13 '14 at 13:03

FirstLoser

23
1
1
3

does it become "select Students.Name as 'Student Name', Marks.Obtained as 'Mark Obtained', Subjects.Name as 'Subject' from Students AS Students inner join Marks as Marks on Students.ID = Marks.StudentName inner join Subjects AS Subjects on Marks.Subject = Marks.ID GO;"?? – Tariq Abdullah Dec 13 '14 at 13:08
Are you writing this in SSMS or Access? – FirstLoser Dec 13 '14 at 13:12
I am writing this in Access – Tariq Abdullah Dec 13 '14 at 13:13
SELECT Students.Name, Marks.Obtained, Subjects.Name FROM Students INNER JOIN (Marks INNER JOIN Subjects ON Marks.Subject = Subjects.ID) ON Students.ID = Marks.StudentID GROUP BY Students.Name, Marks.Obtained, Subjects.Name; – FirstLoser Dec 13 '14 at 13:21
It works but the data is separated. Not grouped by name. It is just slight extension of what I already have .. since this query returns only name of subject in addition. what i need is that subject name should be treated as a column and marks of a particular student be displayed as column value. – Tariq Abdullah Dec 13 '14 at 13:24
Then there must be an error with your joins - I'd question this: Marks.Subject = Subjects.ID Marks.StudentID = Students.ID You also noted a column name in Marks as Subject.ID, but that doesn't conform to Access object naming rules. Check the fields you're joining on. – FirstLoser Dec 13 '14 at 13:26
It works but the data is separated. Not grouped by name. It is just slight extension of what I already have .. since this query returns only name of subject in addition. what i need is that subject name should be treated as a column and marks of a particular student be displayed as column value – Tariq Abdullah Dec 13 '14 at 13:27
SELECT Students.Name, Subjects.Name, Marks.Obtained FROM Students INNER JOIN (Marks INNER JOIN Subjects ON Marks.Subject = Subjects.ID) ON Students.ID = Marks.StudentID GROUP BY Students.Name, Subjects.Name, Marks.Obtained; – FirstLoser Dec 13 '14 at 13:32

SQL Join operation in multiple tables

5 Answers5