I assume that you created your database and ran the SQL scripts as shown in the previous post called SQLite Many-to-Many. I have created a new database called AuthorsBooksInterestsLink.db. I will set up another table and another linking table. Let’s change the table “Link” to “LinkAuthorsBooks”. We need to create a table called Interests and insert some data into it. We also want to create a linking table to link the Interests table to the Authors table.

The linking table is called LinkInterestAuthor.

Here is what the database looks like in DBeaver, without the views. The SQL code for SQLite is shown below that.

All SQL Code So Far

Here is all the SQL we have so far.

CREATE TABLE Authors(
  [AuthorId] INTEGER PRIMARY KEY,
  [AName] TEXT NOT NULL
);
CREATE TABLE Books(
  [BookId] INTEGER PRIMARY KEY,
  [BName] TEXT NOT NULL
);
CREATE TABLE LinkAuthorsBooks(
  [AuthorId] INTEGER NOT NULL,
  [BookId] INTEGER NOT NULL,
  FOREIGN KEY(AuthorId) REFERENCES Authors(AuthorId),
  FOREIGN KEY (BookId) REFERENCES Books(BookId)
);

CREATE TABLE Interests  (
  InterestsId INTEGER PRIMARY KEY,
  IName TEXT);

CREATE TABLE LinkInterestAuthor(
  InterestId INTEGER NOT NULL,
  AuthorId INTEGER NOT NULL,
  FOREIGN KEY(InterestId) REFERENCES Interests(InterestsId),
  FOREIGN KEY(AuthorId) REFERENCES Authors(AuthorId)
);

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 LinkAuthorsBooks (AuthorId, BookId) VALUES (1, 1);
INSERT INTO LinkAuthorsBooks (AuthorId, BookId) VALUES (2, 2);
INSERT INTO LinkAuthorsBooks (AuthorId, BookId) VALUES (1, 3);
INSERT INTO LinkAuthorsBooks (AuthorId, BookId) VALUES (3, 3);

INSERT INTO Interests (InterestsId,IName) VALUES (1,'SQL in depth for SQL Server');
INSERT INTO Interests (InterestsId,IName) VALUES (2,'Snowshoeing');
INSERT INTO Interests (InterestsId,IName) VALUES (3,'Windows Development');

INSERT INTO LinkInterestAuthor (InterestId, AuthorId) VALUES (1,1);
INSERT INTO LinkInterestAuthor (InterestId, AuthorId) VALUES (2,3);
INSERT INTO LinkInterestAuthor (InterestId, AuthorId) VALUES (2,2);
INSERT INTO LinkInterestAuthor (InterestId, AuthorId) VALUES (3,2);

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

CREATE VIEW InterestsAuthors AS
SELECT Interests.IName AS 'Interests', Authors.AName AS 'Authors'
FROM Interests
LEFT JOIN LinkInterestAuthor ON  Interests.InterestsId = LinkInterestAuthor.InterestId
JOIN Authors ON Authors.AuthorId = LinkInterestAuthor.AuthorId
ORDER BY Interests.IName;

Here’s the select statement that links the two three tables Interests, Authors and Books through the two linking tables LinkInterestAuthor and LinkAuthorsBooks.

CREATE VIEW InterestsAuthorsBooks_2 AS
SELECT Interests.IName AS 'Interests', Authors.AName AS 'Author', Books.BName AS 'Book'
FROM Interests
LEFT JOIN LinkInterestAuthor ON Interests.InterestsId = LinkInterestAuthor.InterestId
LEFT JOIN Authors ON LinkInterestAuthor.AuthorId = Authors.AuthorId
LEFT JOIN LinkAuthorsBooks ON Authors.AuthorId = LinkAuthorsBooks.AuthorId
LEFT JOIN Books ON LinkAuthorsBooks.BookId = Books.BookId

The AuthorsBooks view.

The InterestsAuthors view.