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: