[Koha] Need SQL query help, please

[Chris Cormack]

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