Hi everyone,

I am having an issue with a query i am doing for an assignment, i have made an attempt but i know i have not done it right. below is the table structures:

Customer: CustomerCode,[pk] CustomerName

Movie: MovieCode,[pk] Name, Rating

Video: VideoCode,[pk] Name, CustomerSoldTo,[fk1] MovieCode,[fk2]


What i have to do is get the names of all customers that have never bought a video rated r.
Here is my current attempt, which i know is far from right:

SELECT CustomerName 
FROM Customer c, Movie m, Video v
WHERE c.CustomerCode = v.CustomerSoldTo
AND m.MovieCode = v.MovieCode
AND m.Rating NOT LIKE 'R'

Anyone willing to offer any ideas on how to fix this(i'm new to sql)

Thanks very much

SELECT CustomerName 
FROM Customer 
WHERE NOT (Customer.CustomerCode in 
(SELECT c.CustomerCode
 FROM Customer c, Movie m, Video v
 WHERE c.CustomerCode = v.CustomerSoldTo
 AND m.MovieCode = v.MovieCode
 AND m.Rating = 'R'
))