[Koha] SQL help

[Ian Bays]

Hi Patricia.

The three tables are often joined using biblionumber and this might get 
something close to what you want:

SELECT  biblionumber,
        title,
        author,
        copyrightdate
FROM  biblio
LEFT  JOIN  biblioitems  USING  (biblionumber)
LEFT  JOIN  biblio_metadata  USING  (biblionumber)
WHERE  ExtractValue(metadata,  '//datafield[@tag="490"]/subfield[@code="a"]')  LIKE  '%Irish Office pamphlets%'

Of course you can add "ORDER BY" clause to sort the results or just sort 
it in your spreadsheet.

I suspect your series titles will be in the 490 tag (MARC21) rather than 
the 440.

I hope this helps you get to grips with your SQL.

All the best.
Ian


On 19/08/2020 15:11, Patricia Dowling wrote:
> I wonder if anyone in the community can steer me on this basic SQL query? I am a very novice SQL user so apologies in advance!
>
> I want to get a list of bibnumbers for all records with a value of “Irish Office pamphlets” in the 440a tag (series) with title, author and publication date so that I can export this list to excel as a csv file.
>
> I have had a look around the Reports Library but got bogged down between the various tables biblio, items, marcxml etc.)
>
> Thanks in advance!
> Patricia
>
>
>
> Patricia Dowling,
> Research Librarian,
> Oireachtas Library & Research Service,
> Leinster House,
> Dublin 2,
> Ireland
> patricia.dowling at oireachtas.ie
> tel: (01) 6184311
> Central Enquiry Desk: Tel. 00 353 1 6184701
> Email library.and.research at oireachtas.ie<mailto:library.and.research at oireachtas.ie>
>
>
>
>
>
>
>
>
>
>
>
>
> ________________________________
>
> Oireachtas email policy and disclaimer. http://www.oireachtas.ie/parliament/about/oireachtasemailpolicyanddisclaimer/
>
>
> Beartas ríomhphoist an Oireachtais agus séanadh. http://www.oireachtas.ie/parliament/ga/eolas/beartasriomhphoistanoireachtaisagusseanadh/
> _______________________________________________
>
> Koha mailing list  http://koha-community.org
> Koha at lists.katipo.co.nz
> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
>
>

-- 
Ian Bays
Director, PTFS Europe Limited
Content Management and Library Solutions
+44 (0) 7774 995297 (mobile)
skype: ian.bays
email: ian.bays at ptfs-europe.com