[Koha] Need SQL query help, please

[Carol Kresge]

Thanks, but I get an error with both and it won't run
error: 1242 subquery returns more than 1 row

Maybe that is because I should have mentioned that the User attribute
"school" goes to an authorised value category and has a lot of different
values (they go to many different schools...Phongkham, Pasatipatai,
Phasabandith...) Sorry,
do you have another idea?
Thanks,
Carol



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

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



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