Query for multiple many-to-many relations

Question

I am trying to learn normalization on a EDIT: SQL Server 15.x database. My initial table would look like this:

    | TeacherId(PK) | FirstName | LastName | Course  | GroupCode |
    |       1       | Smith     | Jane     | AAA,BBB | A1,A2,B2  |
    |       2       | Smith     | John     | BBB,CCC | A2,B1,B2  |

After normalization I ended up with three tables

    | TeacherId(PK) | FirstName | LastName |       | Course(PK)(FK)  |      | GroupCode(PK)(FK) |
    |       1       | Smith     | Jane     |       |       AAA       |      |        A1         |
    |       2       | Smith     | John     |       |       BBB       |      |        A2         |
                                                   |       CCC       |      |        B1         |
                                                                            |        B2         |

and two joining tables

    | TeacherId(PK) | Course(PK) |       | TeacherId(PK) | GroupCode(PK) |
    |       1       |     AAA    |       |       1       |      A1       |
    |       1       |     BBB    |       |       1       |      A2       |
    |       2       |     BBB    |       |       1       |      B2       |
    |       2       |     CCC    |       |       2       |      A2       |
                                         |       2       |      B1       |
                                         |       2       |      B2       |

The code for these tables is:

    CREATE TABLE [dbo].[Teachers]
    (
[TeacherId] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[FirstName] [nchar](10) NOT NULL,
[LastName] [nchar](10) NOT NULL
    )
    GO
    CREATE TABLE [dbo].[Courses]
    (
[Course] [nchar](10) NOT NULL PRIMARY KEY
    )
    GO

    CREATE TABLE [dbo].[GroupCodes]
    (
[GroupCode] [nchar](10) NOT NULL PRIMARY KEY
    )

    GO

    CREATE TABLE [dbo].[TeacherCourse]
    (
[TeacherId] [int] NOT NULL,
[Course] [nchar](10) NOT NULL,

    PRIMARY KEY (TeacherId, Course),

    CONSTRAINT FK_TeacherCourses FOREIGN KEY (TeacherId)
REFERENCES Teachers(TeacherId),
    CONSTRAINT FK_TeachersCourse FOREIGN KEY (Course)
REFERENCES Courses(Course)
    )

    GO

    CREATE TABLE [dbo].[TeacherGroup]
    (
[TeacherId] [int] NOT NULL,
[GroupCode] [nchar](10) NOT NULL,

    PRIMARY KEY (TeacherId, GroupCode),

    CONSTRAINT FK_TeacherGroups FOREIGN KEY (TeacherId)
REFERENCES Teachers(TeacherId),
    CONSTRAINT FK_TeachersGroup FOREIGN KEY (GroupCode)
REFERENCES GroupCodes(GroupCode)
    )

    GO

    INSERT INTO Teachers(FirstName,LastName)
    VALUES ('Smith','Jane'),('Smith','John')

    GO

    INSERT INTO Courses(Course)
    VALUES ('AAA','BBB','CCC')

    GO

    INSERT INTO GroupCodes(GroupCode)
    VALUES ('A1','A2','B1','B2')

    GO

    INSERT INTO TeacherCourse(TeacherId,Course)
    VALUES ('1','AAA'),('1','BBB'),('2','BBB'),('2','CCC')

    GO

    INSERT INTO TeacherGroup(TeacherId,GroupCode)
    VALUES ('1','A1'),('1','A2'),('1','B2'),('2','A2'),('2','B1'),('2','B2')

    GO

I need to come up with a query that returns each teacher entry in the same form as my initial table:

    | 1 | Smith | Jane | AAA,BBB |  A1,A2,B2  |

So, my question is: how do I make the two joins?

I have tried

    SELECT t.TeacherId AS TeacherId, t.FName AS FirstName, t.LName AS LastName, 
           c.Course AS Course, g.GroupCode AS GroupCode
    FROM TeacherCourse tc, TeacherGroup tg
    JOIN Teachers t ON tc.TeacherId=t.TeacherId
    JOIN Courses c ON tc.Course=c.Course
    JOIN Teachers t ON tg.TeacherId=t.TeacherId
    JOIN GroupCodes g ON tg.GroupCode=g.GroupCode

    ORDER  BY TeacherId

with no success.

Thank you.

EDIT:

I managed to sort out the concatenation using this idea enter link description here

In case this helps anyone, the select code I used is:

SELECT t.TeacherId AS TeacherId, t.FirstName AS FirstName, t.LastName AS LastName, 
(SELECT STUFF((SELECT DISTINCT ', ' + LTRIM(RTRIM(tc.Course)) FROM TeacherCourse tc 
            INNER JOIN Courses c ON tc.Course = c.Course WHERE tc.TeacherId = t.TeacherId
            FOR XML PATH('')),1,1,(''))) AS Courses,
(SELECT STUFF((SELECT DISTINCT ', ' + LTRIM(RTRIM(tg.GroupCode)) FROM TeacherGroup tg 
            INNER JOIN GroupCodes g ON tg.GroupCode = g.GroupCode WHERE tg.TeacherId = t.TeacherId
            FOR XML PATH('')),1,1,(''))) AS Group_Codes
FROM TeacherCourse tc
JOIN TeacherGroup tg ON
    tc.TeacherId = tg.TeacherId
JOIN Teachers t ON 
    tc.TeacherId=t.TeacherId AND tg.TeacherId=t.TeacherId
JOIN Courses c ON 
    tc.Course=c.Course
JOIN GroupCodes g ON 
    tg.GroupCode=g.GroupCode

/* WHERE clause can be inserted here specific results. Example: 
WHERE T.LastName = 'Jane'
*/

GROUP BY t.TeacherId, t.FirstName, t.LastName
ORDER  BY TeacherId

Thank you very much for all your help.

Please only ask a **single** question per question. As this is 3 questions, ask them as separate questions. — Dale K, Jul 08 '22 at 00:46
Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. — Community, Jul 08 '22 at 01:24

Akina · Answer 1 · 2022-07-08T04:39:22.233

Your normalization scheme seems to be incorrect.

You create 3 tables (teacher, course, group) - this is correct.

Now you'd create a table which creates a relation between course and group. This course_group table must be treated as one more entity. This entity stores what course the group should study.

Then you'd create a table which creates a relation between teacher and course_group. And this sets what teacher will study this group with this course.

Also I'd create one more table - the table which sets the relation between teacher and course. This table will store the data about what course the teacher may teach for. This is a pattern, not entity, table. It is used for client-side consistency checking. If a teacher is assigned to teach for a course which not present in this table then the operator will receive a warning that this course is not one registered for this teacher. But the operator may assign nevertheless.

Thank you very much for this. Normalization is definitely not a simple subject and input from experienced users helps a lot in better understanding it. — RGV, Jul 08 '22 at 12:34
I would upvote your answer by my reputation is too tiny to do so. — RGV, Jul 08 '22 at 12:41

score 0 · Answer 2 · answered Jul 08 '22 at 08:36

It needs a lot of cleaning to solve your problem. please let's go step by step. I created your tables here again. At the end, I changed your code in some critical places. Please keep in mind :) MySQL != MSSQL Server

CREATE TABLE Teachers (
  TeacherId int NOT NULL AUTO_INCREMENT,
  FirstName char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  LastName char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (TeacherId)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE Courses (
  Course char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (Course)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE GroupCodes (
  GroupCode char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (GroupCode)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE TeacherGroup (
  TeacherId int NOT NULL,
  GroupCode char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (TeacherId,GroupCode),
  KEY FK_TeachersGroup (GroupCode),
  CONSTRAINT FK_TeacherGroups FOREIGN KEY (TeacherId) REFERENCES Teachers (TeacherId),
  CONSTRAINT FK_TeachersGroup FOREIGN KEY (GroupCode) REFERENCES GroupCodes (GroupCode)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE TeacherCourse (
  TeacherId int NOT NULL,
  Course char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (TeacherId,Course),
  KEY FK_TeachersCourse (Course),
  CONSTRAINT FK_TeacherCourses FOREIGN KEY (TeacherId) REFERENCES Teachers (TeacherId),
  CONSTRAINT FK_TeachersCourse FOREIGN KEY (Course) REFERENCES Courses (Course)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

End Result: Here I corrected some joins, put in group by to summarize the result set by TeacherId, FirstName, and LastName. In the select list I aggregated group codes and course by GROUP_CONCAT() function which does the job well.

SELECT t.TeacherId AS TeacherId, t.FirstName AS FirstName, t.LastName AS LastName, 
GROUP_CONCAT(DISTINCT c.Course separator ', ') as courses, 
GROUP_CONCAT(DISTINCT g.GroupCode separator ', ') as group_codes
FROM TeacherCourse tc
JOIN TeacherGroup tg ON
    tc.TeacherId = tg.TeacherId
JOIN Teachers t ON 
    tc.TeacherId=t.TeacherId AND tg.TeacherId=t.TeacherId
JOIN Courses c ON 
    tc.Course=c.Course
JOIN GroupCodes g ON 
    tg.GroupCode=g.GroupCode
GROUP BY TeacherId, FirstName, LastName
ORDER  BY TeacherId;

Resulting Screenshoot is your initial dataset:

Thank you very much. This is everything I was trying to accomplish and now I finally understand where every entity will be read from and what in what order the joins must be done. I would upvote your answer by my reputation is too tiny to do so. — RGV, Jul 08 '22 at 12:41
Hello, It is nice to hear that your problem is solved. Be well, stay safe, comrade. :) — Ozan Sen, Jul 08 '22 at 12:46
Unfortunately, I am using SQL Server 15.x and neither GROUP_CONCAT, WS_CONCAT or STRING_AGG,, are working. I have tried the STUFF() command and I am getting both teachers with all the courses and all the groups (STUFF() ignores which courses or groups are assigned to a specific TeacherId ) — RGV, Jul 08 '22 at 14:16
I have used `(SELECT STUFF((SELECT DISTINCT ', ' + Course FROM Courses FOR XML PATH('')) ,1,1,(''))) AS courses, (SELECT STUFF((SELECT DISTINCT ', ' + GroupCode FROM GroupCodes FOR XML PATH('')) ,1,1,(''))) AS group_codes` instead of GROUP_CONCAT and it returns 1 Smith Jane AAA , BBB , CCC A1 , A2 , B1 , B2 2 Smith John AAA , BBB , CCC A1 , A2 , B1 , B2 — RGV, Jul 08 '22 at 14:22
@RGV Hello, I think that's something we need to work on! Is there not another function other than XML Path ? I will search and go back to you! — Ozan Sen, Jul 08 '22 at 14:37
Thank you for all your help. I found a solution and updated my original post with it. — RGV, Jul 08 '22 at 15:21
I am pleased to help. Thank you for your cooperation. I appreciate your passion to learn more. Good luck in your projects. — Ozan Sen, Jul 08 '22 at 15:38

Query for multiple many-to-many relations

2 Answers2