SQL for Data Analytics · Many-to-many
Many-to-many
Many-to-Many Relationships10 min
In the video, the first idea for connecting students and classes is a list of student IDs with commas between them, in one column. What goes wrong with that list?
How I'd answer it
In a big class the list gets very long, and it's slow for a computer to read through. Looking up who is in a class stops being a simple match of one ID to another.
There's also nowhere to put a fact about one student in one class, like the grade they got. It can't go with the student, because the student's row doesn't say which class it's for. It can't go with the class either, because the class's row doesn't say which student it's for.
A many-to-many relationship is when a row in one table can be connected to many rows in another table, and a row in that other table can be connected to many rows in the first. An author can write many books, and a book can have more than one author.
A database stores a many-to-many relationship with a third table between the two. People also call it a junction table or an associative table. In this class I call it a joining table. Here is a set of books, their authors, and the joining table BooksAuthors between them.
Books and authors through the joining table
Click an author to see their books. Click a book to see its authors.
Authors
| AuthorID | AuthorName |
|---|
BooksAuthors
| BookID | AuthorID |
|---|
Books
| BookID | Title |
|---|
Each row in BooksAuthors is one author of one book. Book 103, Shadows in the Cosmos, has two authors, so it's on two rows. Orion Stardust, AuthorID 2, wrote two books, so AuthorID 2 is on two rows too.
The joining table turns the many-to-many relationship into two one-to-many relationships:
- Authors to BooksAuthors. One author can be on many rows in
BooksAuthors, and each row has one AuthorID. - Books to BooksAuthors. One book can be on many rows in
BooksAuthors, and each row has one BookID.
Together, those two make the many-to-many. Authors and Books have no column that points at each other. Every connection between an author and a book is a row in BooksAuthors.
To get from a book to its authors, a query goes through BooksAuthors. It joins Books to BooksAuthors on BookID, then joins Authors on AuthorID.
SELECT Books.Title, Authors.AuthorName
FROM Books
JOIN BooksAuthors ON Books.BookID = BooksAuthors.BookID
JOIN Authors ON BooksAuthors.AuthorID = Authors.AuthorID;
The boxes on this page run on the books and authors database. Its tables are in the panel on the right.
Start from Books. Join BooksAuthors on BookID, then join Authors on AuthorID.
There are five books, but that query returns six rows. Where does the extra row come from?
How I'd answer it
The query makes one row for every row in BooksAuthors, and BooksAuthors has six rows. Shadows in the Cosmos has two authors, so it's on two rows of the result, once with each author.
Join Books to BooksAuthors, group by the book, and keep the groups with more than one row. The authors' names aren't needed, so Authors doesn't have to be in the query.
A table alias is a short name for a table, given in the query itself. It's usually an abbreviation of the table's name. In a query with several tables, aliases make the query shorter to write and easier to read.
Here is a query that lists each book's title and its authors, from the Books, BooksAuthors, and Authors tables, with an alias for each table:
SELECT b.Title, a.AuthorName
FROM Books b
JOIN BooksAuthors ba ON b.BookID = ba.BookID
JOIN Authors a ON ba.AuthorID = a.AuthorID;
Books bgivesBooksthe aliasb. Everywhere else in the query,bstands forBooks.BooksAuthors baandAuthors ado the same for the other two tables.b.Title, a.AuthorNameuse the aliases in place ofBooks.TitleandAuthors.AuthorName.b.BookID = ba.BookIDuses them in the join condition too.
Once a table has an alias, the query has to call it by that alias. Books.Title in the query above gets an error, because inside it the table is named b.
b, ba, and a, and run it.Put each alias right after its table's name in FROM and JOIN. Then change every Books., BooksAuthors., and Authors. to b., ba., and a..