CODEWITHSUNDEEP
sqlsqlite
Smile.Hunter
Smile.Hunter

Reputation: 252

How I select record that not appear in another table

Table: Movie

mID title                   year    director
101 Gone with the Wind  1939    Victor Fleming
102 Star Wars           1977    George Lucas
103 The Sound of Music  1965    Robert Wise
104 E.T.                    1982    Steven Spielberg
105 Titanic                 1997    James Cameron
106 Snow White          1937    <null>
107 Avatar                  2009    James Cameron
108 Raiders of the Lost Ark 1981    Steven Spielberg

Table: Rating

rID mID stars   ratingDate
201 101 2   2011-01-22
201 101 4   2011-01-27
202 106 4   <null>
203 103 2   2011-01-20
203 108 4   2011-01-12
203 108 2   2011-01-30
204 101 3   2011-01-09
205 103 3   2011-01-27
205 104 2   2011-01-22
205 108 4   <null>
206 107 3   2011-01-15
206 106 5   2011-01-19
207 107 5   2011-01-20
208 104 3   2011-01-02

I need to fetch movies which are not rate yet. In this case Titanic (mID 105) and Star Wars (mID 102) never get rate in rating table.

I figured out it with

select distinct movie.title from movie,rating where rating.mid!=movie.mid except select distinct movie.title from movie,rating where rating.mid=movie.mid

however I think it might have better (easier/cleaner) way to do.

Upvotes: 0

Views: 110

Answers (4)

HLGEM
HLGEM

Reputation: 96658

I will add another possibility.

Select [list columns here]
from Movie m
where NOT exists (SELECT * FROM RATING r where m.mid = r.mid)

Upvotes: 3

Nicholas Post
Nicholas Post

Reputation: 1857

You could do it like this:

SELECT * FROM Movie WHERE mid NOT IN (SELECT DISTINCT(mid) FROM Rating)

Basically it will select all records from the movie table that are not in the rating table, linking them on the 'mid' column, which I am assuming is a unique identifier.

Upvotes: 3

r.net
r.net

Reputation: 631

Simple: 

SELECT Movies.* FROM Movies LEFT JOIN Rating ON Movies.mID = Rating.mID WHERE Rating.mID IS NULL

Upvotes: 5

Nicolas Rinaudo
Nicolas Rinaudo

Reputation: 6178

If I understood your question properly, that looks like textbook application of outer joins.

Upvotes: 3

Related Questions