0

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.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Sahat
  • 29
  • 1
  • 7
  • 1
    If it's not too late; reconsider group as a table name. It will save headache down the road. – xQbert Sep 24 '20 at 14:28
  • Even if you change the table name, the problem remains with showing the results – Sahat Sep 24 '20 at 17:54
  • What is the exact error message you get. the SQL looks to be correct provided "group" is escaped in backtics – xQbert Sep 24 '20 at 18:35
  • If i omit Join Group G on G.GroupId=S.GroupId i get the result S.Name,L.LessonName,L.Mark from three Table but If i add it, no error message appear and I don't get any result from all the table – Sahat Sep 24 '20 at 18:56
  • No results mean you have no data in the group table with an ID that matches the groupID in student. Change the Join to LEFT join group and I bet you get results. but all the groupnumberse will be blank. Change: FROM `Join Group G on G.GroupId=S.GroupId` to `LEFT Join Group G on G.GroupId=S.GroupId` The inner join is eliminating all the records because there are no matches. The issue is with data; not with the query. – xQbert Sep 24 '20 at 18:59
  • Put another way: Show me a student record that has a GROUP ID, and then show me a group record that has that ID. I bet you can't. When you say I think the error is in this line... we think you get an actual error. No data means the syntax is correct; but data doesn't match what you're asking for. – xQbert Sep 24 '20 at 19:02
  • Why is this tagged with MySQL and Firebird-3.0? Which database system are you really using? What is the error you get? In almost all SQL dialects, `group` is a reserved word, so you need to quote it; how depends on the database system you're using. – Mark Rotteveel Sep 25 '20 at 08:00

2 Answers2

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
GMB
  • 216,147
  • 25
  • 84
  • 135
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)

xQbert
  • 34,733
  • 2
  • 41
  • 62
  • thank you for your help but don't work it appear only S.Name L.LessonName L.Mark but G.GroupNumber nothing appear if i change to RIGHT JOIN Group G on G.GroupId=S.GroupId it appears only Groups !! – Sahat Sep 24 '20 at 19:52
  • even i write this code ''' SELECT S.Name, G.GroupNumber FROM Student s LEFT JOIN Group G on G.GroupId=S.GroupId ''' only S.Name appear SELECT S.Name, G.GroupNumber FROM Student s LEFT JOIN Group G on G.GroupId=S.GroupId ''' only G.GroupNumber and with inner Join nothing appear – Sahat Sep 24 '20 at 20:08
  • thank you very much it works perfectly the problem was in the foreign key GroupId in the Student Table – Sahat Sep 25 '20 at 08:43