Excerpts from Mark Alexander's message of 2020-01-04 20:27:31 -0500: > I think this might work, except that I can't figure out how to get a > patron's list of overdue books into the generated letter. The SQL > would need to produce a CSV file with a single row for each patron, > containing the patron name, address, and an arbitrarily long list of > overdue books.
A kind person replied off-list and gave me the magic I needed: GROUP_CONCAT. Thanks! In case anyone is interested, here is the SQL I ended up using: SELECT p.cardnumber, CONCAT(p.firstname, ' ', p.surname) as name, p.address, CONCAT(p.city, ', ', p.state, ' ', p.zipcode) as city, GROUP_CONCAT(CONCAT_WS(' ', REPLACE(b.title, ' /', ''), CONCAT('$', i.replacementprice)) SEPARATOR '; ') as overdues FROM borrowers p LEFT JOIN issues c ON (p.borrowernumber=c.borrowernumber) LEFT JOIN items i ON (c.itemnumber=i.itemnumber) INNER JOIN biblio b ON (i.biblionumber=b.biblionumber) WHERE (TO_DAYS(curdate())-TO_DAYS(date_due)) >= <<Days overdue>> GROUP BY cardnumber _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha