How can I join 4 table?

Question

I have 4 tables, three are many to many relationship:

• Student(StudID,GroupId,Name,....)
• Lesson(LessID,LessonName,Mark)
• StudentLesson(StudID,LessID)

and the relationship between student and Group is One to Many

• Student(StudID,Name,....)
• Group(GroupId,GroupNumber)

What I want is how select Name, LessonName, Mark, GroupNumber

select S.Name, L.LessonName, L.Mark, G.GroupNumber from Student s
join StudentLesson SL on SL.StudId = S.StudId
join Lesson L on SL.LessID = L.LessID
Join Group G on G.GroupId = S.GroupId

I think the error in this line Join Group G on G.GroupId=S.GroupId, because when I omit it, it works between many to many but between one to many it didn't work.

Answer 1

Based on Comments: the query is fine; you lack data that matches the results you're after.

1. There are no students with a groupID
2. There are no students with a groupID matching GROUPID in the group table.

To prove this out you could simply make the last join a LEFT Join provided you have no where clause with limits on Group.

FROM:

select S.Name,L.LessonName,L.Mark,G.GroupNumber from Student s
join StudentLesson SL on SL.StudId=S.StudId
join  Lesson L on SL.LessID =L.LessID
Join Group G on G.GroupId=S.GroupId

TO:

SELECT S.Name, L.LessonName, L.Mark, G.GroupNumber 
FROM Student s
INNER JOIN StudentLesson SL on SL.StudId=S.StudId
INNER JOIN Lesson L on SL.LessID =L.LessID
LEFT JOIN Group G on G.GroupId=S.GroupId

This will show you all students w/ lessons and groupNumber if the groupID's match; but i'm betting they will all be NULL.

So are you after all students regardless if they have lessons or groups if so your inner joins should be left. If you're only after students that have lessons and belong to groups then they all need to be inner joins. Just depends on what you're after!

Left join will say include all records from the prior data joins, and only those that match from this join (to group in the example)

Answer 2

group is a reserved word, so it needs to be quoted. In MySQL, you can use backticks:

select S.Name, L.LessonName, L.Mark, G.GroupNumber 
from Student       S
join StudentLesson SL on SL.StudId = S.StudId
join  Lesson       L  on SL.LessID = L.LessID
Join  `Group`      G  on G.GroupId = S.GroupId