On 4 April 2012 15:12, Carol Kresge <[email protected]> wrote: > They only go to one school each. another possibility is they do have more > than 1 item checked out > Writing this without the data to test, but I *think* this will do it.
SELECT surname, firstname, mobile, email, city, biblio.title, biblioitems.itemtype, issues.date_due, (select borrower_attributes.attribute from borrower_attributes where code = 'School' and borrower_attributes.borrowernumber=borrowers.borrowernumber) as School FROM borrowers LEFT OUTER JOIN borrower_attributes on (borrower_attributes.borrowernumber = borrowers.borrowernumber) LEFT JOIN issues on (issues.borrowernumber = borrowers.borrowernumber) LEFT JOIN items on (items.itemnumber = issues.itemnumber) LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber) WHERE issues.date_due < DATE_SUB(now(), INTERVAL 8 DAY) AND issues.returndate IS NULL AND borrowers.mobile = ' ' AND borrowers.email = ' ' GROUP BY borrowers.borrowernumber,items.itemnumber ORDER BY borrowers.surname asc; Fingers crossed Chris _______________________________________________ Koha mailing list http://koha-community.org [email protected] http://lists.katipo.co.nz/mailman/listinfo/koha
