Hi Kerrie, if you don't mind hardcoding the borrowernumbers this should do the trick. SELECT b.borrowernumber AS 'Borrowernumber', CONCAT(b.firstname, ' ', b.surname) AS 'Name', COUNT(i.issue_id) AS 'Outstanding loans' FROM borrowers b LEFT JOIN issues i ON b.borrowernumber = i.borrowernumber WHERE b.borrowernumber IN ( 123, 456, 789 ) GROUP BY b.borrowernumber Alternatively, you could use a BETWEEN in the WHERE clause, if the borrowernumbers are consecutive. Regards, Holger -----Ursprüngliche Nachricht----- Von: Koha <koha-bounces@lists.katipo.co.nz> Im Auftrag von Jonathan Druart Gesendet: Mittwoch, 27. Mai 2020 11:00 An: Kerrie Stevens <kerrie.stevens@ac.edu.au> Cc: koha@lists.katipo.co.nz Betreff: Re: [Koha] SQL help? Hello Kerrie, I am not sure this is what you want, but if you create a SQL report with this query you will see the patrons with outstanding loans: SELECT b.borrowernumber, concat(b.surname, ' ', b.firstname), count(*) FROM issues LEFT JOIN borrowers b on b.borrowernumber=issues.borrowernumber GROUP BY b.borrowernumber; Regards, Jonathan Le mer. 27 mai 2020 à 02:24, Kerrie Stevens <kerrie.stevens@ac.edu.au> a écrit :
Every year, we need to confirm graduating students have no outstanding loans so they can graduate. And every year I think I must ask for some help in doing it more efficiently than searching borrower numbers one by one... I'd like to be able to copy & paste a number of borrower numbers into search box and get back a table showing those with outstanding loans: (borrower number, name, and number of current loans) Can cutting & pasting or searching on multiple borrower numbers be done in SQL reports? This is way beyond my basic level of SQL understanding, so any advice/assistance would be greatly appreciated.
Thank you and have a great day wherever you may be!
Kerrie Stevens AALIA(CP), MAppSci(LibMgt), BBus(Info&LibMgt) Director of Library Services, Alphacrucis College Librarian - Melbourne Campus HDR Liaison Librarian _______________________________________________
Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
_______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha