creating a report for patrons without an email that have items overdue
Can anyone tell me how to "combine" the following reports? We want to create a report that gives us a list of patrons who do not have email accounts and have items overdue. (information from http://wiki.koha.org/doku.php?id=sql_library) Sharon Moreland Circulation Overdue materials SELECT borrowers.surname, borrowers.firstname, borrowers.cardnumber, issues.date_due, (TO_DAYS(curdate())-TO_DAYS(date_due)) AS 'days overdue', items.itemcallnumber, items.barcode, biblio.title, biblio.author FROM borrowers LEFT JOIN issues ON (borrowers.borrowernumber=issues.borrowernumber) LEFT JOIN items ON (issues.itemnumber=items.itemnumber) LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber) WHERE (TO_DAYS(curdate())-TO_DAYS(date_due)) >= '28' AND homebranch = 'LIBRARY' ORDER BY issues.date_due asc, borrowers.surname ASC Complete Sharon Moreland Patron Missing e-mails SELECT cardnumber, surname, firstname, branchcode, debarred, dateexpiry FROM borrowers WHERE ' ' IN (email) Melanie <http://www.facebook.com/pages/Salina-KS/Salina-Public-Library/917121931 39?ref=s>
Just need to add the WHERE clause from the second one to the first: SELECT borrowers.surname, borrowers.firstname, borrowers.cardnumber, issues.date_due,(TO_DAYS(curdate())-TO_DAYS(date_due)) AS 'days overdue',items.itemcallnumber, items.barcode, biblio.title, biblio.author FROM borrowers LEFT JOIN issues ON (borrowers.borrowernumber=issues.borrowernumber) LEFT JOIN items ON (issues.itemnumber=items.itemnumber) LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber) WHERE (TO_DAYS(curdate())-TO_DAYS(date_due)) >= '28' AND homebranch = 'LIBRARY' AND ' ' IN (borrowers.email) ORDER BY issues.date_due asc, borrowers.surname ASC Nicole 2009/8/12 Melanie Hedgespeth <melanie@salpublib.org>
SELECT borrowers.surname, borrowers.firstname, borrowers.cardnumber, issues.date_due,
(TO_DAYS(curdate())-TO_DAYS(date_due)) AS 'days overdue',
items.itemcallnumber, items.barcode, biblio.title, biblio.author
FROM borrowers
LEFT JOIN issues ON (borrowers.borrowernumber=issues.borrowernumber)
LEFT JOIN items ON (issues.itemnumber=items.itemnumber)
LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
WHERE (TO_DAYS(curdate())-TO_DAYS(date_due)) >= '28' AND homebranch = 'LIBRARY'
ORDER BY issues.date_due asc, borrowers.surname ASC
participants (2)
-
Melanie Hedgespeth -
Nicole Engard