[Koha] Need SQL query help, please

[Chris Cormack]

On 4 April 2012 15:12, Carol Kresge <carol.kresge at gmail.com> 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