select from rental table in mysql to display items currently rented by user

Question

If you know about sakila sample database, then what is the statement to select items currently rented by a user. If not here is a code explanation:

CREATE TABLE IF NOT EXISTS `rentals` (
 `item_id` int(10) unsigned NOT NULL,
 `user_id` int(10) unsigned NOT NULL,
 `last_change_date` date NOT NULL,
 PRIMARY KEY  (`item_id`,`user_id`,`last_change_date`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Notice there is no return date, this is because the last renter has indefinite rental time, until somebody requests this item and it's transferred to the new renter for indefinite time as well. A user can rent more than one item and there is only one piece of each item so an item_id cannot go to two users at the same time.

I would like to display currently rented items per user_id.

Answer 1

SELECT r1.*
FROM   rentals r1
       LEFT JOIN rentals AS r2
         ON r1.item_id = r2.item_id
            AND r1.last_change_date < r2.last_change_date
WHERE  r2.last_change_date IS NULL  

This is a classic sql question. The answer is explained here.

Using

INSERT INTO rentals (item_id, user_id, last_change_date) VALUES (1, 1, '2009-01-01'), (1, 3, '2009-11-10'), (3, 3, '2009-02-13'), (3, 5, '2010-05-11'), (5, 5, '2010-06-04'), (7, 7, '2010-06-04'), (9, 9, '2010-06-04');

as play-data, to understand the method, look at the output of

SELECT r1.*,r2.*
FROM   rentals r1
       LEFT JOIN rentals AS r2
         ON r1.item_id = r2.item_id
            AND r1.last_change_date < r2.last_change_date

+---------+---------+------------------+---------+---------+------------------+
| item_id | user_id | last_change_date | item_id | user_id | last_change_date |
+---------+---------+------------------+---------+---------+------------------+
|       1 |       1 | 2009-01-01       |       1 |       3 | 2009-11-10       | 
|       1 |       3 | 2009-11-10       |    NULL |    NULL | NULL             | 
|       3 |       3 | 2009-02-13       |       3 |       5 | 2010-05-11       | 
|       3 |       5 | 2010-05-11       |    NULL |    NULL | NULL             | 
|       5 |       5 | 2010-06-04       |    NULL |    NULL | NULL             | 
|       7 |       7 | 2010-06-04       |    NULL |    NULL | NULL             | 
|       9 |       9 | 2010-06-04       |    NULL |    NULL | NULL             | 
+---------+---------+------------------+---------+---------+------------------+

The first 3 columns refer to r1's columns, the last 3 refer to r2's.

As you can see, whenever there is no r2.last_change_date which is greater than r1.last_change_date, the value is NULL. Those are the rows where r1.last_change_date is greatest. So to find the rows you want, you use the condition

WHERE  r2.last_change_date IS NULL