This post follows from a previous post called SQL Server Many-To-Many. I created a SQLite database in DB Browser under my Social Enterprise folder on my computer. The database is called AuthorsBooksLink.db. You can follow along and create your own database and call it whatever you like.

Let’s get straight to the SQL script to create the table, insert a bit of data and run our first select query. Create a non-production SQLite database and copy the code to try it yourself. After you create a new database go to the Execute SQL tab and run the code below (copy and paste it).

create table Authors(
  [AuthorId] INTEGER PRIMARY KEY,
  [AName] TEXT NOT NULL
);
create table Books(
  [BookId] INTEGER PRIMARY KEY,
  [BName] TEXT NOT NULL
);
create table Link(
  [AuthorId] INTEGER NOT NULL,
  [BookId] INTEGER NOT NULL,
  FOREIGN KEY(AuthorId) REFERENCES Authors(AuthorId),
  FOREIGN KEY (BookId) REFERENCES Books(BookId)
);
INSERT INTO Authors (AuthorId, AName) VALUES (1, 'Itzik Ben-Gan');
INSERT INTO Authors (AuthorId, AName) VALUES (2, 'Adam Nathan');
INSERT INTO Authors (AuthorId, AName) VALUES (3, 'Adam Machanic');
INSERT INTO Books (BookId, BName) VALUES (1, 'T-SQL Fundamentals');
INSERT INTO Books (BookId, BName) VALUES (2, 'WPF 4.5 Unleashed');
INSERT INTO Books (BookId, BName) VALUES (3, 'T-SQL Querying');
INSERT INTO Link (AuthorId, BookId) VALUES (1, 1);
INSERT INTO Link (AuthorId, BookId) VALUES (2, 2);
INSERT INTO Link (AuthorId, BookId) VALUES (1, 3);
INSERT INTO Link (AuthorId, BookId) VALUES (3, 3);

CREATE VIEW AuthorsBooks AS
SELECT Authors.AName AS 'Author', Books.BName AS 'Book'
FROM Authors
LEFT JOIN Link ON  Authors.AuthorId = Link.AuthorId
JOIN Books ON Books.BookId = Link.BookId
ORDER BY Authors.AName;

Here is what we get when we run the new view in DB Browser.

We can see that Machanic and Ben-Gan wrote the book T-SQL Querying. Two authors (actually there were 4 of them) wrote one book. We can also see that Ben-Gan wrote two books. One author wrote two different books (he actually wrote more than that). We have a many-to-many relationship that we’ve handled by using a linking table. The SELECT query is a bit tricky though.

LEFT JOIN

Have another look at the CREATE VIEW code. Here we are showing Authors, and their books. What if we wanted ALL authors even if they have no books? We change the query to accomplish that. Let’s prove that by first adding an author that has no entry in the Link table (linking them to a book). Now let’s change the query. I called it AllAuthorsBooks.

INSERT INTO Authors (AuthorId, AName) VALUES (4, 'Joe Silent');

CREATE VIEW AllAuthorsBooks AS
SELECT Authors.AName AS 'Author', Books.BName AS 'Book'
FROM Authors
LEFT JOIN Link ON Authors.AuthorId = Link.AuthorId
LEFT JOIN Books ON Books.BookId = Link.BookId
ORDER BY Authors.AName;

If I run the above code and then go to the Browse Data tab in DB Browser I can see the result of our new view, as shown below in the screenshot.

Books

Let’s flip it and look at all Books.

CREATE VIEW AllBooksAuthors AS
SELECT Books.BName AS 'Book', Authors.AName AS 'Author' 
FROM Books
LEFT JOIN Link ON Books.BookId = Link.BookId
LEFT JOIN Authors ON Authors.AuthorId = Link.AuthorId
ORDER BY Books.BName;

However, we don’t have a book without an author. Let’s add some data. We’ll just add one book with anything in the Link table.

INSERT INTO Books (BookId, BName) VALUES (4, 'A New Book');

So we have shown that we have the correct query to show all books and their authors by using two left joins and starting with the table Books.

Authors that have Multiple Books

Let’s create a view.

CREATE VIEW AuthorsWithMultipleBooks AS
SELECT Author, COUNT(*) AS 'Number of Books > 1'
FROM AllAuthorsBooks
GROUP BY Author
HAVING COUNT(*) > 1

Here’s what that looks like.