A WORKING MYSQL QUERY SELF TABLE JOIN

Written by James McDonald

October 8, 2012

I have a table holding the names of `items` to be loaned out

mysql> select * from items;
+----+-------+
| id | name  |
+----+-------+
|  1 | item1 |
|  2 | item2 |
|  3 | item3 |
|  4 | item4 |
|  5 | item5 |
+----+-------+
5 rows in set (0.00 sec)

I have another `histories` table that keeps track of when the items are loaned out and returned:

mysql> select id, map_id, date_loaned, date_returned from histories;
+----+---------+-------------+---------------+
| id | item_id | date_loaned | date_returned |
+----+---------+-------------+---------------+
|  1 |       1 | 2012-10-08  | 2012-10-09    |
|  2 |       1 | 2012-05-08  | 2012-06-04    |
|  3 |       2 | 2012-09-08  | 2012-09-13    |
|  4 |       1 | 2012-07-08  | 2012-07-10    |
|  5 |       3 | 2012-09-14  | 2012-10-08    |
|  6 |       2 | 2012-10-07  | 2012-10-08    |
|  7 |       3 | 2012-10-08  | 2012-10-08    |
|  8 |       4 | 2012-10-08  | 2012-10-08    |
|  9 |       4 | 2012-10-09  | 2012-10-10    |
| 10 |       1 | 2012-10-10  | 2012-10-11    |
| 12 |       3 | 2012-10-12  | NULL          |
| 14 |       2 | 2012-10-16  | NULL          |
+----+---------+-------------+---------------+
12 rows in set (0.00 sec)

I want to be able to select all the items that are currently `in` meaning anything in the items table without a histories record and anything in the histories table having one or more history records but doesn’t have a NULL date_returned value.

This SQL is working on MYSQL 5.1.63-0+squeeze1 (Debian)

/* ITEMS IN SQL WORKING COPY */
SELECT items.name, t1.id, t1.item_id, t1.date_loaned, t1.date_returned, 
t2.id, t2.item_id, t2.date_loaned, t2.date_returned
FROM items
LEFT JOIN histories t1 ON items.id = t1.item_id
LEFT JOIN histories t2
ON t1.item_id = t2.item_id AND t1.date_loaned < t2.date_loaned 
WHERE ( t2.id IS NULL AND t1.date_returned IS NOT NULL) OR ( t1.id IS NULL )
GROUP BY t1.item_id, t1.date_loaned DESC
ORDER BY t1.date_returned ASC

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

You May Also Like…