[Koha] Need SQL query help, please

Chris Cormack chris at bigballofwax.co.nz
Wed Apr 4 09:32:45 NZST 2012


Ohh,

This will actually work better

SELECT
surname,
firstname,
mobile,
email,
city,
biblio.title,
biblioitems.itemtype,
issues.date_due,
(select borrower_attributes.attribute from borrower_attributes where
code = 'School' group by 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;

And avoid double ups

Chris


More information about the Koha mailing list