[Koha] Need SQL query help, please

[Carol Kresge]

Yes! perfect...thanks.

On Wed, Apr 4, 2012 at 10:14 AM, Chris Cormack <chris at bigballofwax.co.nz>wrote:

> 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
>



-- 
supporting Lao youth in their quest for a brighter future....
The Language Project…an American 501(c)3 charity

Website:         www.thelanguageproject.org

Gallery:           http://laophotographers.zenfolio.com

Lao address: PO Box 905,  Luang Prabang, Laos
Lao mobile:   856 20 97122771
US address:  PO Box 13201, Tallahassee, FL 32317