Last updated Nov 17, 2024

Given our bookstore database example from the previous lesson:

To retrieve data that spans across these three tables, we use the SQL JOIN operation twice. Let’s see how we can use the JOIN command to fetch the list of books along with their authors’ names now that we’re representing the data with a many-to-many relationship.

1
2
3

SELECT Books.Title, Authors.AuthorName FROM Books
JOIN BooksAuthors ON Books.BookID = BooksAuthors.BookID
JOIN Authors ON BooksAuthors.AuthorID = Authors.AuthorID;

• This command retrieves the Title from the Books table and the AuthorName from the Authors table to display book titles alongside their authors’ names.
• It also sets Books as the table from which the query starts, establishing the context for the JOIN operations that follow.

• The first JOIN operation. It joins the Books table with the BooksAuthors joining table, based on the BookID. This operation matches each book with its corresponding entries in the BooksAuthors table.

• The second JOIN operation. It joins the intermediate result (which includes the information from Books and BooksAuthors) with the Authors table. The join is based on the AuthorID, linking each entry in the BooksAuthors table with its respective author.

Use the Magic Store Database (SQL - W3 Magic Store Database Overview) to complete the practice questions for week.

1. Display Order Details with Product Names: Write a query to display details of each order, including the order ID, the date it was placed, and the names of the products in the order.

• SQL - W7 Many-to-Many Relationships
• SQL - W7 JOIN Tables with Many-to-Many Relationships
• Next: SQL - W7 Table Aliasing
• SQL - W7 Practice Assignment