Member Avatar for trashr0x

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
  • 91 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by baki100
Member Avatar for baki100

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.

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.