[Koha] SQL Report help

[Jeramey Valley]

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
valleyj at bcreek.org