Home > OS >  Query for multiple many-to-many relations
Query for multiple many-to-many relations

Time:07-09

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 Click the image to see the result

  • Related