How do I get rental users in SQL without displaying what has already been returned

Question

I have a 3 tables database. This is a DVD rental database.

I would like to know what DVDs are currently rented to users. I use PL/SQL.

What I can't resolve is to keep the returned DVDs from showing up, especially if the same user has taken out the same DVD again.

  User Table:      DVD Table:                            Rent table:
| ID | Name  |   | ID | Name  |   | ID | USER_ID | DVD_ID | RENT_RETURN | Rent_RETURN_DATE | 
| -- | ----- |   | -- | ----- |   | -- | ------- | ------ | ----------- | ---------------- |
| 1  | USER1 |   | 1  | DVD1  |   | 1  |    1    |    1   |      -1     |     2020.01.01   |   
| 2  | USER2 |   | 2  | DVD2  |   | 2  |    1    |    1   |       1     |     2020.02.01   |
| 3  | USER3 |   | 3  | DVD3  |   | 3  |    1    |    1   |      -1     |     2020.03.01   | 
| 4  | USER4 |   | 4  | DVD4  |   | 4  |    1    |    2   |      -1     |     2020.04.01   | 
                                  | 5  |    2    |    3   |      -1     |     2020.05.01   | 
                                  | 6  |    3    |    4   |      -1     |     2020.06.01   | 
                                  | 7  |    3    |    2   |      -1     |     2020.07.01   | 
                                  | 8  |    3    |    4   |       1     |     2020.08.01   | 

What I want to reach:

| USER_NAME | DVD_NAME |  RENT_DATE |
| --------- | -------- | ---------- |
|     1     |    1     | 2020.03.01 | 
|     1     |    2     | 2020.04.01 | 
|     2     |    3     | 2020.05.01 | 
|     3     |    2     | 2020.07.01 | 

I tried this but yes it's not enough:

SELECT U.NAME, D.NAME, R.RENT_RETURN_DATE
FROM USER U, DVD D, RENT R
WHERE U.ID = R.USER_ID
AND D.ID = R.DVD_ID
AND R.RENT_RETURN = 1;

Thanks in advance for your help!

Answer 1

One option would be using ROW_NUMBER() analytic function such as

SELECT user_name, dvd_name, rent_return_date 
  FROM (SELECT ROW_NUMBER() OVER 
                           (PARTITION BY user_id, dvd_id 
                                ORDER BY rent_return_date DESC) AS rn,
               u.name AS user_name, d.name AS dvd_name, r.*
          FROM rent r
          JOIN "user" u
            ON r.user_id = u.id
          JOIN dvd d
            ON r.dvd_id = d.id
         WHERE rent_return = -1)
 WHERE rn = 1  

or directly use aggregation

SELECT u.name AS user_name,
       d.name AS dvd_name,
       MAX(rent_return_date) AS rent_return_date
  FROM rent r
  JOIN "user" u
    ON r.user_id = u.id
  JOIN dvd d
    ON r.dvd_id = d.id
 WHERE rent_return = -1
 GROUP BY u.name,d.name