Reputation: 49
I am quite new to sql, and trying to solve this kind of question, and have no idea if I am doing it right.
I have two tables(bold is Primary key and italic is Foreign key):
FILM (**FILM_ID**, TITLE, CERTIFICATE, *TYPE_ID*, RENTAL_FEE) and
FILMTYPE (**TYPE_ID**, TYPE_NAME)
I need to retrieve the titles and rental fees for all the films with their certificate being ‘12’ and their type being ‘Drama’.
My query is:
SELECT Film.Title, Film.Rental_fee
FROM Film
INNER JOIN Filmtype
ON Film.Type_Id = Filmtype.Type_Id
WHERE Film.Certificate=12 AND Filmtype.Type_Name='Drama'
As I do this from the paper book, with no answers, I need an advice if this query is right or I am doing it completely wrong.
Upvotes: 1
Views: 64
Reputation: 6038
You need not write join..
Try this.
SELECT *
FROM film
WHERE TYPE_ID =
(SELECT TYPE_ID FROM FILMTYPE WHERE TYPE_NAME = 'Drama'
)
Upvotes: 0
Reputation: 726539
Yes, you are doing it absolutely right. One common shortcut which helps in writing more complex queries is using table aliases instead of full table names, like this:
SELECT f.Title, f.Rental_fee
FROM Film f
INNER JOIN Filmtype t ON f.Type_Id = t.Type_Id
WHERE f.Certificate=12 AND t.Type_Name='Drama'
It hardly matters in this query because the number of tables and conditions is small, but using short aliases helps when the number of tables goes up. However, it is optional in this case, so your query is perfectly fine.
Upvotes: 2