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