Joining three tables in MySql using the sakila database

Hello I am trying to join three tables using the sakila database. It returns the column names i provided but no information. Any tips would be greatly appreciated!

USE sakila;

SELECT c.name 'Category Name'
     , f.title 'Film Title'
     , f.description 'Film Description'
     , f.release_year 'Release Year'
  FROM sakila.category c
  JOIN sakila.film_category fc 
    ON c.category_id = fc.category_id 
  JOIN sakila.film f 
    ON f.film_id = fc.film_id

1 answer

  • answered 2018-02-13 00:38 spencer7593

    The most likely explanation for the observed behavior is that there are no rows in the database that satisfy the conditions.

    The query will return zero rows if any one of the three tables is empty i.e. contains zero rows. The query will also return zero rows if there are no rows that "match", for example, if the values of the category_id column in film_category is NULL or is a value that doesn't match a value in the category_id column of category.


    First, check if any of the tables is empty:

    SELECT COUNT(1) FROM film ;
    SELECT COUNT(1) FROM film_category ;
    SELECT COUNT(1) FROM category ;
    

    If all of the tables contain rows, next check if there are any rows that don't match. For example:

    SELECT fc.category_id  
      FROM film_category fc
      LEFT
      JOIN category c
        ON c.category_id = fc.category_id
     WHERE fc.category_id IS NULL
    

    Similarly for the film_id column.