hue manny hue manny - 1 year ago 47
MySQL Question

How to get films that were rented on Thursday

I am trying to get the First names Last names of customers and the films that were rented on Thursday but what I put in is gives me an Empty set.

This is where I am getting table info from

select first_name, last_name, title
from sakila_customer join sakila_rental using(customer_id)
join sakila_inventory using(inventory_id) join sakila_film using(film_id)
where sakila_rental.rental_date = DAYNAME('Thursday');

Answer Source

You are applying the function in the wrong way:

where dayname(sakila_rental.rental_date) = 'Thursday'

Your query would also be easier to write and read if you used table aliases:

 select c.first_name, c.last_name, f.title 
 from sakila_customer c join
      sakila_rental r
      using (customer_id) join 
      sakila_inventory i
      using (inventory_id) join
      sakila_film f
      using (film_id)
 where dayname(r.rental_date) = 'Thursday';

If you are learning SQL< then now is a good time to start good habits such as using table aliases.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download