Simple JOINs recap
7. JOINS – ambiguous columns
Various kinds of JOINs
Filtering with LEFT JOIN and RIGHT JOIN
Summary

Instruction

Great!

When joining tables, you'll sometimes find that they share a few column names. For example, it is common for tables to have an id column. In this case, if two or more tables you're joining share some column names, you must always refer to those identical column names using the table name followed by a dot and the name of the column you want to select.

SELECT
  employee.id as employee_id,
  position.id as position_id
FROM position
JOIN employee
  ON position.id = employee.position_id

In the SELECT clause we specify the columns as employee.id and position.id to avoid ambiguity. If we don't do that, the database won't know which id column we are referring to.

We also aliased the columns to employee_id and position_id, respectively, to make the result more readable.

Exercise

For each book, show its title, adaptation title, adaptation year, and publication year.

Consider only books with a rating lower than the rating of their corresponding adaptation. Additionally, show only those books for which an adaptation was released at most four years after the book was published.

Rename the title column from the book table to book_title, and the title column from the adaptation table to adaptation_title.

Stuck? Here's a hint!

You can filter your query by various conditions. Simply use the WHERE keyword for the first condition and the AND keyword before all others.

To select only adaptations that were created at most four years after the corresponding book was published, you will have to write:

adaptation.release_year - book.publish_year <= 4

Type:

SELECT
  book.title AS book_title,
  adaptation.title AS adaptation_title,
  book.publish_year,
  adaptation.release_year
FROM book
JOIN adaptation
  ON book.id = adaptation.book_id
WHERE adaptation.release_year - book.publish_year <= 4
  AND book.rating < adaptation.rating

Console

Code editor

Result

TableConsole