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

[Elaine Bradtke]

Thanks, that worked perfectly
I want the biblio number because we're using it as part of our matching
rules during import. The biblio numbers are being added into the
appropriate records pre-upload, because I can't write a matching rule that
includes subtitle and edition.

This will become even more useful when we import data from a branch which
duplicates about 80% of what's already in the main collection.
Thanks again!
Elaine

On Thu, Oct 25, 2012 at 2:04 AM, Bernardo Gonzalez Kriegel <
bgkriegel at gmail.com> wrote:

> Elaine,
> try this
>
> select
>   b.biblionumber, author, title,
>   ExtractValue(marcxml, '//datafield[@tag="245"]/subfield[@code="b"]') as
> subtitle,
>   ExtractValue(marcxml, '//datafield[@tag="250"]/subfield[@code="a"]') as
> edition,
>   copyrightdate, place, publishercode, isbn
> from
>   items as a
> left join
>   biblioitems as b on (a.biblioitemnumber = b.biblioitemnumber)
> left join
>   biblio as c on (b.biblionumber = c.biblionumber)
> where
>   itype = 'BK' and holdingbranch = 'VWML'
> order by author asc;
>
> Now, Why need biblionumber? That's one of Koha's internal numbers.
>
> Regards,
> Bernardo
>
> --
> Bernardo Gonzalez Kriegel
> bgkriegel at gmail.com
>
>
>
> On Wed, Oct 24, 2012 at 6:23 PM, Elaine Bradtke <eb at efdss.org> wrote:
>
>> 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
>>
>
>


-- 
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)