Thanks everyone! I added the limit by biblio number and this works: SELECT biblioitems.biblionumber,biblio.title,biblio.copyrightdate, ExtractValue(m.metadata, '//datafield[@tag="260"]/subfield[@code="c"]') AS text FROM items LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber) LEFT JOIN biblio_metadata m ON (biblio.biblionumber=m.biblionumber) WHERE items.biblionumber >= '17920' AND items.biblionumber <= '17959' ORDER BY biblio.copyrightdate asc
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) On Mon, Oct 9, 2017 at 9:42 AM, Jonathan Druart < jonathan.dru...@bugs.koha-community.org> wrote: > Sorry, I badly pasted the query. > Try: > SELECT biblioitems.biblionumber,biblio.title,biblio.copyrightdate, > ExtractValue(m.metadata, '//datafield[@tag="260"]/subfield[@code="c"]') > AS text FROM items LEFT JOIN biblioitems on > (items.biblioitemnumber=biblioitems.biblioitemnumber) > LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber) LEFT > JOIN biblio_metadata m ON (biblio.biblionumber=m.biblionumber) ORDER BY > biblio.copyrightdate asc; > > > On Mon, 9 Oct 2017 at 13:10 Elaine Bradtke <e...@efdss.org> wrote: > >> this time it says: You have an error in your SQL syntax; check the >> manual that corresponds to your MySQL server version for the right syntax >> to use near 'JOIN biblio_metadata m ON (biblio.biblionumber=m.biblionumber) >> ORDER BY biblio' at line 7 >> I think we're getting closer. >> >> Elaine Bradtke >> Data Wrangler >> VWML >> English Folk Dance and Song Society | http://www.efdss.org >> Cecil Sharp House, 2 Regent >> <https://maps.google.com/?q=2+Regent's+Park+Road,+London+NW1+7AY&entry=gmail&source=g> >> 's Park Road, London NW1 7AY >> <https://maps.google.com/?q=2+Regent's+Park+Road,+London+NW1+7AY&entry=gmail&source=g> >> Tel +44 (0) 20 7485 2206 <+44%2020%207485%202206> (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) >> >> On Thu, Oct 5, 2017 at 8:11 AM, Jonathan Druart < >> jonathan.dru...@bugs.koha-community.org> wrote: >> >>> Since 17196 (17.05) you need to join on biblio_metadata >>> Try the following query: >>> SELECT biblioitems.biblionumber, biblio.title, biblio.copyrightdate, >>> ExtractValue(m.metadata, >>> '//datafield[@tag="260"]/subfield[@code="c"]') >>> AS text >>> FROM items >>> LEFT JOIN biblioitems on (items.biblioitemnumber= >>> biblioitems.biblioitemnumber) >>> LEFT JOIN biblio on (biblioitems.biblionumber= >>> LEFT JOIN biblio_metadata m ON (biblio.biblionumber=m.biblionumber) >>> ORDER BY biblio.copyrightdate asc; >>> >>> On Thu, 5 Oct 2017 at 11:43 Elaine Bradtke <e...@efdss.org> wrote: >>> >>>> It gives the error message: >>>> *The following error was encountered:* >>>> The database returned the following error: >>>> Unknown column 'biblioitems.marcxml' in 'field list' >>>> >>>> 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 >>>> <https://maps.google.com/?q=2+Regent's+Park+Road,+London+NW1+7AY&entry=gmail&source=g> >>>> Tel +44 (0) 20 7485 2206 <+44%2020%207485%202206> (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) >>>> >>>> On Thu, Oct 5, 2017 at 3:16 PM, Holger Meissner < >>>> holger.meiss...@hs-gesundheit.de> wrote: >>>> >>>> > Hi Elaine, >>>> > >>>> > how about this: >>>> > >>>> > SELECT >>>> > biblioitems.biblionumber, >>>> > biblio.title, >>>> > ExtractValue(biblioitems.marcxml, '//datafield[@tag="260"]/ >>>> subfield[@code="c"]') >>>> > AS text >>>> > FROM items >>>> > LEFT JOIN biblioitems on (items.biblioitemnumber= >>>> > biblioitems.biblioitemnumber) >>>> > LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber) >>>> > WHERE >>>> > items.biblionumber >= '17920' AND items.biblionumber <= '17959' >>>> > ORDER BY text ASC >>>> > >>>> > >>>> > -----Ursprüngliche Nachricht----- >>>> > Von: Koha [mailto:koha-boun...@lists.katipo.co.nz] Im Auftrag von >>>> Elaine >>>> > Bradtke >>>> > Gesendet: Donnerstag, 5. Oktober 2017 15:08 >>>> > An: koha >>>> > Betreff: [Koha] Help needed with SQL report >>>> > >>>> > I need a report that gives bib number, title and 260$c from a set of >>>> > records within a range of biblio numbers. >>>> > >>>> > This is what the wizard gives me: >>>> > SELECT biblioitems.biblionumber,biblio.title,biblio.copyrightdate >>>> FROM >>>> > items LEFT JOIN biblioitems on >>>> > (items.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN >>>> biblio on >>>> > (biblioitems.biblionumber=biblio.biblionumber) WHERE >>>> items.biblionumber >>>> > >= '17920' AND items.biblionumber <= '17959' ORDER BY >>>> > >biblio.copyrightdate >>>> > asc >>>> > >>>> > But it's only giving the year (presumably from the 008?) I need the >>>> > actual text of the 260$c field. >>>> > >>>> > Any help would be greatly appreciated. Thanks! >>>> > I looked for a way I could configure the csv output from a list to >>>> give me >>>> > that information, but didn't find it . >>>> > Elaine Bradtke >>>> > Data Wrangler >>>> > VWML >>>> > English Folk Dance and Song Society | http://www.efdss.org Cecil >>>> Sharp >>>> > House, 2 Regent >>>> <https://maps.google.com/?q=2+Regent's+Park+Road,+London+NW1+7AY&entry=gmail&source=g> >>>> 's Park Road, London NW1 7AY >>>> <https://maps.google.com/?q=2+Regent's+Park+Road,+London+NW1+7AY&entry=gmail&source=g> >>>> > Tel +44 (0) 20 7485 2206 <+44%2020%207485%202206> (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@lists.katipo.co.nz >>>> > https://lists.katipo.co.nz/mailman/listinfo/koha >>>> > >>>> _______________________________________________ >>>> Koha mailing list http://koha-community.org >>>> Koha@lists.katipo.co.nz >>>> https://lists.katipo.co.nz/mailman/listinfo/koha >>>> >>> >> _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha