On 4 April 2012 00:32, Carol Kresge <[email protected]> wrote: > I have a query to find borrowers with late books that do not have email > addresses or mobiles. Now I am trying to modify it so it will show their > school from attributes if they have one but won't knock them off the list > if they don't. My modification below shows only those with no email and no > mobile and a school but knocks people off the list who have no school > attribute. How can I modify it so it includes no mobile no email and lists > school for those who have one but still includes those who don't? > > SELECT > borrowers.surname,borrowers.firstname,borrowers.mobile,borrowers.email,borrowers.city, > biblio.title,biblioitems.itemtype,issues.date_due,borrower_attributes.attribute > FROM borrowers > LEFT 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 = > ' ' AND borrower_attributes.code = 'School' ORDER BY borrowers.surname asc; > Hi Carol
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') 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 = ' ' ORDER BY borrowers.surname asc; Chris _______________________________________________ Koha mailing list http://koha-community.org [email protected] http://lists.katipo.co.nz/mailman/listinfo/koha
