[Koha] SQL help?

[Holger Meissner]

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 at lists.katipo.co.nz> Im Auftrag von Jonathan Druart
Gesendet: Mittwoch, 27. Mai 2020 11:00
An: Kerrie Stevens <kerrie.stevens at ac.edu.au>
Cc: koha at 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 at 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 at lists.katipo.co.nz
> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
_______________________________________________

Koha mailing list  http://koha-community.org Koha at lists.katipo.co.nz
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha