Epus
Epus

Reputation: 49

SQL join, select and where

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

Answers (2)

Nisar
Nisar

Reputation: 6038

You need not write join..

Try this.

SELECT *
FROM film
WHERE TYPE_ID =
  (SELECT TYPE_ID FROM FILMTYPE WHERE TYPE_NAME = 'Drama'
  )

DEMO

Upvotes: 0

Sergey Kalinichenko
Sergey Kalinichenko

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

Related Questions