Ignore the last one. This one will work better. Hope this is helpful. SELECT b.cardnumber, b.surname, b.firstname, i.issuedate AS 'Current Issue', o.issuedate AS 'Old Issue' FROM borrowers b LEFT JOIN issues i ON (b.borrowernumber = i.borrowernumber) LEFT JOIN old_issues o ON (b.borrowernumber = o.borrowernumber) WHERE i.issuedate BETWEEN <<Issue Date Between (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>> OR o.issuedate BETWEEN <<Issue Date Between (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>> GROUP BY b.cardnumber
On Wed, Feb 20, 2019 at 12:11 PM Joe Sikowitz <j...@flo.org> wrote: > You could also add in old issues which you probably would want so that you > get returned items: > > SELECT b.cardnumber, b.surname, b.firstname, i.issuedate AS 'Current > Issue', o.issuedate AS 'Old Issue' > FROM borrowers b > INNER JOIN issues i ON (b.borrowernumber = i.borrowernumber) > INNER JOIN old_issues o ON (b.borrowernumber = o.borrowernumber) > WHERE i.issuedate or o.issuedate BETWEEN <<Issue Date Between > (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>> > GROUP BY b.cardnumber > > Joe > > On Wed, Feb 20, 2019 at 11:35 AM Joe Sikowitz <j...@flo.org> wrote: > >> Something like this may work: >> >> SELECT b.cardnumber, b.surname, b.firstname, i.issuedate >> FROM borrowers b >> INNER JOIN issues i ON (b.borrowernumber = i.borrowernumber) >> WHERE i.issuedate BETWEEN <<Issue Date Between (yyyy-mm-dd)|date>> AND >> <<and (yyyy-mm-dd)|date>> >> GROUP BY b.cardnumber >> >> Joe >> >> On Wed, Feb 20, 2019 at 11:24 AM Chris Brown <ch...@stayawake.co.uk> >> wrote: >> >>> Gentle Reader, >>> >>> We want to get a list of all patrons for who we do *not *have email >>> addresses but who are "active" (for example, have checked a book out in >>> the >>> last 6 months) >>> >>> I know how to get a list of patrons with no email address but my >>> knowledge >>> of SQL and the database schema aren't good enough to write the "AND have >>> checked a book out in the last 6 months" part of the query. >>> >>> Is there a kind soul out there who could help? We are using Koha 17.11 >>> >>> Thanks and Best Regards, >>> >>> Chris Brown >>> _______________________________________________ >>> Koha mailing list http://koha-community.org >>> Koha@lists.katipo.co.nz >>> https://lists.katipo.co.nz/mailman/listinfo/koha >>> >> >> >> -- >> Joe Sikowitz >> Collaborative Services Librarian >> Fenway Library Organization >> (617) 989-5031 >> j...@flo.org >> > > > -- > Joe Sikowitz > Collaborative Services Librarian > Fenway Library Organization > (617) 989-5031 > j...@flo.org > -- Joe Sikowitz Collaborative Services Librarian Fenway Library Organization (617) 989-5031 j...@flo.org _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
