Member Avatar for mrblippy

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

  • 2 Contributors
  • 1 Reply
  • 106 Views
  • 12 Hours Discussion Span
  • Latest Post Latest Post by smantscheff
Member Avatar for smantscheff 
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'
))
Edited by smantscheff because: n/a
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.