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).
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.
