CODEWITHSUNDEEP
sqldatabaseschema
batsta13
batsta13

Reputation: 539

How do I do this SQL query

The following are the tables I am working with:

Movie(mID, title, year, director)

Reviewer(rID, name)

Rating(rID, mID, stars, ratingDate)

which statement would I use to display all movies that have no ratings. I am new to the SQL language and can't quite see how this can be done. From what I have been told this is a relatively simple query.

Upvotes: 1

Views: 88

Answers (4)

Conrad Frix
Conrad Frix

Reputation: 52645

There are three ways to do this. My preference is for an Anti-Join, which is a LEFT JOIN with a where clause test for IS NULL on the right side of the join. 

SELECT * 
FROM 
   Movie
   LEFT JOIN Rating
    ON moive.Mid = rating.mid
WHERE
    rating.mid is null

Upvotes: 2

Chetter Hummin
Chetter Hummin

Reputation: 6817

You can use the NOT EXISTS clause. A good example for Oracle is at http://www.dba-oracle.com/sql/t_subquery_not_in_exists.htm. The syntax may vary depending on your database

Upvotes: 0

Marty Cortez
Marty Cortez

Reputation: 2343

SELECT * FROM Movie WHERE Movie.mid NOT IN (SELECT mID FROM Rating)

Upvotes: 5

tallseth
tallseth

Reputation: 3675

select * from movie where mID not in (select mId from rating)

Upvotes: 0

Related Questions