CODEWITHSUNDEEP
mysqlsqljoindata-retrieval
av192
av192

Reputation: 492

mysql inner join to relate all tuples

I have the following tables:

copy(movie_id,copy_id)
rented(copy_id,outdate,returndate)

If a movie is rented out, the returndate is set to null in the database.

There will be multiple copies of the same movie. for a single movie_id, we can have multiple copy_id.

I need to retrieve the movies that have been rented out completely, i.e. all the copies of the movie have been rented out or put in another way-all the copies of a movie are present in the rented table with the returndate set as null.

I have tried inner joins, but am not being able to relate all the tuples in the copy table to the rented table.

Each copy has a globally unique copy_id. So copies of 2 different movies cannot have the same copy_id.

If the copy has never been rented, it will not show up in the list, however it means that the movie is still in stock, as it has never been rented. This should not show up.

The same movie and copy will definitely appear in rented multiple times, if it has been rented more than once.

Upvotes: 1

Views: 270

Answers (3)

wvdz
wvdz

Reputation: 16651

This turned out a little bit more difficult than I thought. I believe this is the correct answer.

"All movies, for which for all of its copies there exists a rented where returndate is null"

In mathematical notation (A=for All, E=there exists):

{ m : M | ( A c : C | c.movie_id = m.movie_id @ ( E r : R | r.copy_id = c.copy_id @ r.returndate = null ) ) @ m.movie_id }

Which can be rephrased to:

"All movies, for which there doesn't exist a copy, for which there doesn't exist a rented where returndate is null"

Which translates to the following SQL.

SELECT DISTINCT m.movie_id
FROM Copy m
WHERE NOT EXISTS
  (SELECT 1 FROM Copy c
  WHERE c.movie_id = m.movie_id
  AND NOT EXISTS
    (SELECT 1 FROM Rented r
    WHERE r.copy_id = c.copy_id
    AND returndate IS NULL)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270663

You can do what you want by using a left join and aggregation with a having clause. Then, count the number of records with no return date and compare that to the number of copies:

SELECT c.movie_id
FROM copy c LEFT JOIN
     rented r
     ON c.copy_id = r.copy_id
GROUP BY c.movie_id
HAVING SUM(r.returndate IS NULL) = COUNT(DISTINCT c.copy_id)

Note the use of SUM() for the comparison. This counts the number of rows where the value is "true".

The above query assumes that a single copy cannot be rented more than once at a time. A reasonable assumption, but always worth checking. An alternative having clause takes this into account:

HAVING count(distinct case when r.returndate is null then c.copy_id end) = count(distinct c.copy_id)

Upvotes: 1

Brian Driscoll
Brian Driscoll

Reputation: 19635

You can group by movie_id and count where returndate is null:

SELECT DISTINCT movie_id
FROM copy
JOIN rented
ON copy.copy_id = rented.copy_id
GROUP BY copy.movie_id HAVING COUNT(rented.returndate IS NULL) = 0

Upvotes: 0

Related Questions