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
