Thanks so much, was exactly what I needed. Final solution:
SELECT p.cardnumber, p.surname, p.firstname, DATE_FORMAT(c.issuedate, "%m/%d/%Y") AS Issue_Date, DATE_FORMAT(c.date_due, "%m/%d/%Y") AS Due_Date, h.attribute AS teacher, (TO_DAYS(curdate())-TO_DAYS( date_due)) AS 'days overdue', b.title, b.author, i.itemcallnumber, i.replacementprice, ExtractValue(bi.marcxml,'//datafield[@tag="020"]/subfield[@code="c"]') AS price, b.biblionumber, i.barcode FROM borrowers p LEFT JOIN issues c ON (p.borrowernumber=c.borrowernumber) LEFT JOIN items i ON (c.itemnumber=i.itemnumber) LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber) LEFT JOIN borrower_attributes g ON (p.borrowernumber=g.borrowernumber) LEFT JOIN borrower_attributes h ON (p.borrowernumber=h.borrowernumber) LEFT JOIN biblioitems bi ON (b.biblionumber = bi.biblionumber) WHERE c.branchcode = <<Branch|branches>> AND (TO_DAYS(curdate())-TO_DAYS( date_due)) >= <<Days overdue>> AND h.code="HOMEROOM" ORDER BY g.attribute, h.attribute, p.surname ASC -- Regards, Jeramey Valley Network Manager, Bullock Creek Schools [email protected] > On Apr 24, 2015, at 2:04 PM, Barton Chittenden <[email protected]> > wrote: > > Jeremy, > > You can join biblioitems to biblio using biblionumber. Add this right > before your WHERE clause: > > LEFT JOIN biblioitems bi ON (b.biblionumber = bi.biblionumber) > > Once you've done that, you can add > > b.biblionumber, > ExtractValue(bi.marcxml,'//datafield[@tag="020"]/subfield[@code="c"]') > AS heading > > As fields to SELECT, > > Then add > > AND length(ExtractValue(bi.marcxml, > '//datafield[@tag="020"]/subfield[@code="c"]')) != 0 > > as part of your WHERE clause. > > I'll let you work out the 'order by' part - you'll know better than I > will how you want your results ordered. > > --Barton > > On Fri, Apr 24, 2015 at 1:44 PM, Jeramey Valley <[email protected]> wrote: >> Sure there’s a simple solution, not very good with SQL… >> >> This query works great: >> >> SELECT p.cardnumber, p.surname, p.firstname, >> DATE_FORMAT(c.issuedate, "%m/%d/%Y") AS Issue_Date, DATE_FORMAT(c.date_due, >> "%m/%d/%Y") AS Due_Date, h.attribute AS teacher, >> (TO_DAYS(curdate())-TO_DAYS( date_due)) AS 'days overdue', >> b.title, b.author, i.itemcallnumber, i.replacementprice, >> i.barcode >> FROM borrowers p >> LEFT JOIN issues c ON (p.borrowernumber=c.borrowernumber) >> LEFT JOIN items i ON (c.itemnumber=i.itemnumber) >> LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber) >> LEFT JOIN borrower_attributes g ON (p.borrowernumber=g.borrowernumber) >> LEFT JOIN borrower_attributes h ON (p.borrowernumber=h.borrowernumber) >> WHERE c.branchcode = <<Branch|branches>> AND (TO_DAYS(curdate())-TO_DAYS( >> date_due)) >= <<Days overdue>> AND h.code="HOMEROOM" >> ORDER BY g.attribute, h.attribute, p.surname ASC >> >> >> I need to also add to the above: >> >> SELECT biblionumber, >> ExtractValue(marcxml,'//datafield[@tag="020"]/subfield[@code="c"]') AS >> heading >> FROM biblioitems >> WHERE length(ExtractValue(marcxml, >> '//datafield[@tag="020"]/subfield[@code="c"]')) != 0 >> ORDER BY heading >> >> Basically need to combine the two, how to do so? >> >> -- >> >> Regards, >> >> Jeramey Valley >> Network Manager, Bullock Creek Schools >> [email protected] >> >> >> >> >> _______________________________________________ >> Koha mailing list http://koha-community.org >> [email protected] >> http://lists.katipo.co.nz/mailman/listinfo/koha _______________________________________________ Koha mailing list http://koha-community.org [email protected] http://lists.katipo.co.nz/mailman/listinfo/koha
