Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2010
    Posts
    3

    Unanswered: Help with Sakila

    Hello everyone!

    I have an assignment which uses the default database sakila.
    Here is what I have to do:

    List the names of the films whose titles start with W rented by
    customers whose last name starts with A and the number of times each
    such film was rented (by a customer whose last name starts with A).

    I'm working on it and I have hit a wall and here is what I have come up with so far:

    Code:
    select  film.title, customer.last_name , count(customer.last_name)  from film, customer,
    rental, inventory where film.title like 'W%' and film.film_id =
    inventory.film_id and rental.inventory_id = inventory.inventory_id and
    rental.customer_id = customer.customer_id and customer.last_name like 'A%' group by
    film.title, customer.last_name;
    If anyone can help me see what I am doing wrong I would really appreciate it!
    Thank you so much for your help!
    Mike.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the first thing you need to do is learn how to write joins

    have a look at this --
    Code:
      FROM film
    INNER
      JOIN inventory
        ON inventory.film_id = film.film_id
    INNER
      JOIN rental
        ON rental.inventory_id = inventory.inventory_id
    INNER
      JOIN customer
        ON customer.customer_id = rental.customer_id  
       AND customer.last_name LIKE  'A%'
     WHERE film.title LIKE  'W%'
    please notice how the sequence in which the tables are joined make sense logically

    you start with films which begin with W

    then you find all the inventory for those films, then all the rentals for those inventories, then all the rental customers who start with A

    at each step along the way, it's very clear as to which table is being added, and on what column

    notice how the ON clauses mention the column from the table being joined first, and then equate that column to a column of a table previously mentioned

    this style makes queries very easy to understand, and to debug
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2010
    Posts
    3
    Thank you that does make sense and it is much easier to read. I was getting so confused trying to read my own code.

    Code:
    SELECT film.title, customer.last_name
    FROM film
    INNER 
    JOIN inventory ON inventory.film_id = film.film_id
    INNER 
    JOIN rental ON rental.inventory_id = inventory.inventory_id
    INNER 
    JOIN customer ON customer.customer_id = rental.customer_id
    AND customer.last_name LIKE  'A%'
    WHERE film.title LIKE  'W%'
    Does this query look like it would accomplish this task (minus the counting part), it does to me:

    List the names of the films whose titles start with W rented by
    customers whose last name starts with A and the number of times each
    such film was rented (by a customer whose last name starts with A).
    Now how in the world do I count how many times the film was rented?

    Thank you for your help!
    Last edited by hoobajue; 01-10-10 at 21:04. Reason: missed bottom of code

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by hoobajue View Post
    Does this query look like it would accomplish this task (minus the counting part), it does to me:
    hmm.... what happened when you tested it?



    Quote Originally Posted by hoobajue View Post
    Now how in the world do I count how many times the film was rented?
    hint: COUNT(*) and GROUP BY
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2010
    Posts
    3
    It's a large database so I'm not sure if the results I'm getting are correct.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by hoobajue View Post
    It's a large database so I'm not sure if the results I'm getting are correct.
    then break it down into steps

    first, use a simple query pull out all the films that start with W, and grab their ids

    then run a query against the inventory table, pulling out the inventory_ids, using WHERE film_id IN ( list of film ids from first query )

    then run a query against the rentals table, pulling out the customer ids, using WHERE inventory_id IN ( list of inventory ids from previous query )

    finally, pull all the customers based on these rental ids, sort them by name, and compare the list of those customers whose name starts with A

    this process will allow you to trust the JOIN query's result

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •