Do you have a database that has two tables that have a many-to-many relationship? What do I mean by many-to-many? For example, do you have a database that stores books and authors? How about one that stores movies and actors? An author of a book may have written many books. A single book may have more than one author. This is an example of many-to-many. How do you set up the tables in a database properly to represent a many-to-many relationship?

Junction Table

You can create a junction table (aka linking table or intermediary table or associative table) between the two tables, Authors and Books. A linking table is also called an associative table. You end up with three tables. The key to understanding how it works is the junction table. Once the data is available, we can approach the query it from two directions. We could look at all the Authors and all their Books, or we could look at all the Books and who wrote them. Below is a the SQL code to create a very simple example of a many-to-many relationship using a linking table, that you could use in a non-production database.

create table Authors(
  [AuthorId] int NOT NULL,
  [Name] varchar (20) NOT NULL
 CONSTRAINT [PK_Authors] PRIMARY KEY CLUSTERED 
 ([AuthorId] ASC)
)
create table Books(
  [BookId] int NOT NULL,
  [Name] varchar (20) NOT NULL
 CONSTRAINT [PK_Books] PRIMARY KEY CLUSTERED 
 ([BookId] ASC)
)
create table Link(
  [AuthorId] int NOT NULL,
  [BookId] int NOT NULL
 CONSTRAINT [PK_AuthorBooks] PRIMARY KEY CLUSTERED 
 ([AuthorId] ASC,[BookId] ASC)
)

I have inserted a little bit of data into each of the three tables. Below is a screen shot of a couple of queries against those tables.

Below are the queries that created the above results in SSMS.

select Authors.[Name], Books.[Name]
from Authors
inner join Link on Authors.AuthorId = Link.AuthorId
inner join Books on Link.BookId = Books.BookId
order by Authors.[Name], Books.[Name]

select Books.[Name], Authors.[Name]
from Books 
inner join Link on Link.BookId = Books.BookId
inner join Authors on Authors.AuthorId = Link.AuthorId
order by Books.[Name], Authors.[Name]

Below is the results of three select queries, the first against Authors, the second on Books and the third on the Linking table.

SQL Script

Below is a script that you can use in your non-production database to create the three tables and populate them with some data. Feel free to do this. I’ve named the database AuthorsBooks1.

create database AuthorsBooks1
go
use AuthorsBooks1
create table Authors(
  [AuthorId] int NOT NULL,
  [Name] varchar (20) NOT NULL
 CONSTRAINT [PK_Authors] PRIMARY KEY CLUSTERED 
 ([AuthorId] ASC)
)
create table Books(
  [BookId] int NOT NULL,
  [Name] varchar (20) NOT NULL,
  [Rating] [real] NULL,
  [Pages] [int] NULL
 CONSTRAINT [PK_Books] PRIMARY KEY CLUSTERED 
 ([BookId] ASC)
)
create table Link(
  [AuthorId] int NOT NULL,
  [BookId] int NOT NULL
 CONSTRAINT [PK_AuthorBooks] PRIMARY KEY CLUSTERED 
 ([AuthorId] ASC,[BookId] ASC)
)
INSERT [dbo].[Authors] ([AuthorId], [Name]) VALUES (1, N'Itzik Ben-Gan')
INSERT [dbo].[Authors] ([AuthorId], [Name]) VALUES (2, N'Dejan Sarka')
INSERT [dbo].[Authors] ([AuthorId], [Name]) VALUES (3, N'Adam Machanic')
INSERT [dbo].[Authors] ([AuthorId], [Name]) VALUES (4, N'Kevin Farlee')
INSERT [dbo].[Authors] ([AuthorId], [Name]) VALUES (5, N'Hugo Cornelius')
GO
INSERT [dbo].[Books] ([BookId], [Name], [Rating], [Pages]) VALUES (101, N'T-SQL Querying', 4.7, 864)
INSERT [dbo].[Books] ([BookId], [Name], [Rating], [Pages]) VALUES (102, N'T-SQL Fundamentals', 4.7, 464)
INSERT [dbo].[Books] ([BookId], [Name], [Rating], [Pages]) VALUES (103, N'Expert SQL Server 05', 3.8, 472)
GO
INSERT [dbo].[Link] ([AuthorId], [BookId]) VALUES (1, 101)
INSERT [dbo].[Link] ([AuthorId], [BookId]) VALUES (1, 102)
INSERT [dbo].[Link] ([AuthorId], [BookId]) VALUES (2, 101)
INSERT [dbo].[Link] ([AuthorId], [BookId]) VALUES (3, 101)
INSERT [dbo].[Link] ([AuthorId], [BookId]) VALUES (3, 103)
INSERT [dbo].[Link] ([AuthorId], [BookId]) VALUES (4, 101)
GO

We can create a view in SQL Server to save the query. Below is the code to that.

CREATE VIEW [dbo].[View_1]
AS
SELECT dbo.Authors.AuthorId, dbo.Authors.Name, dbo.Link.BookId, dbo.Books.Name AS Title, dbo.Books.Pages, dbo.Books.Rating
FROM     dbo.Authors INNER JOIN
                  dbo.Link ON dbo.Authors.AuthorId = dbo.Link.AuthorId INNER JOIN
                  dbo.Books ON dbo.Link.BookId = dbo.Books.BookId
GO

If you were to run a simple select against the query, you would see the following.