[Koha] SQL help needed: borrower info needed in 2 contexts

[Steve Campbell]

Here's one for you SQL hotshots. We took a report from the library and modified it slightly. It shows all items that are checked out and also have holds on them. Here it is:

SELECT b.title, i.itemcallnumber AS "Call Number", i.barcode AS "Barcode", date(r.timestamp) AS "Hold Date", 
               bo.cardnumber AS "Held For", bo.firstname, bo.surname
FROM reserves r 
LEFT JOIN biblio b ON (r.biblionumber=b.biblionumber) 
LEFT JOIN items i ON (i.biblionumber=b.biblionumber) 
LEFT JOIN borrowers bo ON (r.borrowernumber=bo.borrowernumber)
WHERE i.itemnumber  IN (SELECT issues.itemnumber FROM issues) 
ORDER BY r.timestamp ASC
It works as it is, but we would like to add something. It already tells us who has the hold on the item, but we would also like to see the borrower cardnumber, firstname, and surname for the patron who has the item checked out, i.e. in issues. How would we do that?

Steve Campbell
Converse Free Library
Lyme, New Hampshire, USA