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 < [email protected]> 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 > [email protected] > > > > On Wed, Oct 24, 2012 at 6:23 PM, Elaine Bradtke <[email protected]> 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 >> [email protected] >> 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) _______________________________________________ Koha mailing list http://koha-community.org [email protected] http://lists.katipo.co.nz/mailman/listinfo/koha
