[Koha] Help needed with report - how to extract subtitle and edition?

[Nicole Engard]

This should do the trick:

SELECT
 b.biblionumber, b.author ,b.title, ExtractValue(m.marcxml,
'//datafield[@tag="245"]/subfield[@code="b"]') as subtitle,
b.copyrightdate, m.place, m.publishercode, m.isbn
FROM items i
 LEFT JOIN biblioitems m on
(i.biblioitemnumber=m.biblioitemnumber)
LEFT JOIN biblio b on (m.biblionumber=b.biblionumber)
WHERE i.itype='BK' AND i.holdingbranch='VWML'
ORDER BY b.author asc

Thanks
Nicole


On Wed, Oct 24, 2012 at 5:51 PM, mh_zalabany at hotmail.com
<mh_zalabany at hotmail.com> wrote:
> I think in this case you need to make the report using the xml field in biblio items table because the rest of sub fields you will find there
>
> Sent from my HTC
>
> ----- Reply message -----
> From: "Elaine Bradtke" <eb at efdss.org>
> To: "koha" <koha at lists.katipo.co.nz>
> Subject: [Koha] Help needed with report - how to extract subtitle and   edition?
> Date: Wed, Oct 24, 2012 11:23 pm
>
>
> I'm trying to catch duplicate items in data before I upload it (ISBN won't
> help in this case). In order to do that I've written a report to list the
> books currently in Koha, and will compare them with my external files.
>  However, if I can't include the subtitle (MARC 245 b) I will get too many
> false positives (we have lots of things with the same title, but different
> subtitles)
> It would also help if I could  also have the 250 a (edition)
>
>
> This is what I have:
> SELECT
>  biblio.biblionumber,biblio.author,biblio.title,biblio.copyrightdate,biblioitems.place,biblioitems.publishercode,biblioitems.isbn
> FROM items
>  LEFT JOIN biblioitems on
> (items.biblioitemnumber=biblioitems.biblioitemnumber)
> LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber)
> WHERE items.itype='BK' AND items.holdingbranch='VWML'
> ORDER BY biblio.author asc
>
>
> This works fine, but doesn't include the 245 b or the 250 a  Can anyone
> help?
> --
> Elaine Bradtke
> Data Wrangler
> VWML
> English Folk Dance and Song Society | http://www.efdss.org
> Cecil Sharp House, 2 Regent's Park Road, London NW1 7AY
> Tel    +44 (0) 20 7485 2206 (This number is for the English Folk Dance and
> Song Society in London, England. If you wish to phone me personally, send
> an e-mail first. I work off site)
> --------------------------------------------------------------------------
> Registered Company No. 297142
> Charity Registered in England and Wales No. 305999
> ---------------------------------------------------------------------------
> "Writing about music is like dancing about architecture"
> --Elvis Costello (Musician magazine No. 60 (October 1983), p. 52)
> _______________________________________________
> Koha mailing list  http://koha-community.org
> Koha at lists.katipo.co.nz
> http://lists.katipo.co.nz/mailman/listinfo/koha
>
> _______________________________________________
> Koha mailing list  http://koha-community.org
> Koha at lists.katipo.co.nz
> http://lists.katipo.co.nz/mailman/listinfo/koha