0

I have a mysql db with 3 tables (this is supposed to be a db for a dvd rental store)

1) MOVIE
PK MovieID
Title
Type
Year
Producer

2) CLIENT
PK ClientID
Name
Address
City

3) RENT
PK RentID
Date
FK MovieID
FK ClientID
Cost

The exercise told us we had to identify primary/foreign keys and draw the relational model, create the tables, fill them in (random data) and answer some questions using sql queries.

I am stuck in the following question for the past 3 hours:

Show pairs of clients/producers where the client never rented a movie of that producer.
So if client A never rented a movie of producer B then the result should be
A B (A never rented a movie of Producer B)

Any help on this? this assignment had 4 tasks, managed to do the 4 but this one seems hard to do and mind breaking.

Best regards

2
Contributors
1
Reply
2
Views
7 Years
Discussion Span
Last Post by baki100
0

dude i tried this in oracle and it worked but try this:

select c.Name, m.Producer
from movie m, client c, rent r
where c.cleintID + m.movieID = r.MovieID + r.ClientID
and m.movieID = r.MovieID
and c.cleintID = r.ClientID

this might be a bit sloppy but i had to do this in my head as i don't have mysql installed on my pc. the above query should work given the id's are numeric if not you might have to concat them.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.