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

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!

sqloraclegreatest-n-per-group


Solution 1:[1]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Drashti thakkar

Related Questions