[Koha] SQL Report help

[Barton Chittenden]

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 <valleyj at bcreek.org> 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
> valleyj at bcreek.org
>
>
>
>
> _______________________________________________
> Koha mailing list  http://koha-community.org
> Koha at lists.katipo.co.nz
> http://lists.katipo.co.nz/mailman/listinfo/koha