Last updated Nov 17, 2024

We can still utilize the clauses from previous lessons to filter and sort on joined data.

For this example, we’ll use the Authors and Books tables with a one-to-many relationship. Each author can write multiple books, but each book has only one author.

Create a query that joins these tables and:

1. Filters to show only books written by a specific author (e.g., George Orwell).
2. Sorts the results by the book’s title.

1
2
3
4

SELECT Books.Title, Authors.AuthorName FROM Books
JOIN Authors ON Books.AuthorID = Authors.AuthorID
WHERE Authors.AuthorName = 'George Orwell'
ORDER BY Books.Title;

1. SELECT Clause: Retrieves the book titles and their corresponding author names.
2. JOIN Operation: Joins the Books and Authors tables using the AuthorID as the common field.
3. WHERE Clause: Filters the results to include only those books written by ‘Leo Tolstoy’.
4. ORDER BY Clause: Sorts the resulting list of books by their titles in ascending order.

JOIN must come before our WHERE and ORDER BY clauses because we need to have access to both the AuthorName and Title in order to filter and sort them.

3. Write a query to list all products supplied by ‘Enchanted Wares Ltd.’ including the supplier name along with the product names and prices. Sort the results by product name (Z to A).

4. Write a query to list all suppliers and their products, sorted by the supplier’s name and product price.

• SQL - W3 Table JOINs - Introduction
• SQL - W3 Table JOINs - DROP Tables
• SQL - W3 Magic Store Database Overview
• SQL - W3 Table JOINs - One-to-Many Relationships
• SQL - W3 Table JOINs - JOIN Tables with One-to-Many Relationships
• SQL - W3 Table JOINs - Filter and Sort with JOINs
• Next: SQL - W3 One-to-Many JOINs - Practice Assignment