Hi Vanda, You need to connect to the biblioitems table which holds the MARC record in its "marcxml" field, then use the SQL ExtractValue <https://mariadb.com/kb/en/library/extractvalue/> function to pull out these specific MARC fields. This process is necessary because not every single MARC tag is in its own database field, you have to get most of the data out of the field that storess the full record. So a report like this should accomplish what you want: SELECT i.itemnumber, b.title, b.author, i.itemcallnumber, i.barcode, v.lib, ExtractValue(bi.marcxml, '//datafield[@tag="205"]/subfield[@code="a"]') as Edition, ExtractValue(bi.marcxml, '//datafield[@tag="210"]/subfield[@code="c"]') as Publisher, ExtractValue(bi.marcxml, '//datafield[@tag="210"]/subfield[@code="d"]') as Date, FROM items i LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber) LEFT JOIN biblioitems bi ON (i.biblioitemnumber=bi.biblioitemnumber) LEFT JOIN authorised_values v ON (i.itemlost=v.authorised_value) WHERE i.itemlost != 0 AND v.category='LOST' The three ExtractValue lines pull out the fields you specified while the one additional join adds the biblioitems table to the query. Note that, in newer Koha versions starting with 17.05 the MARCXML record is stored in the biblio_metadata <http://schema.koha-community.org/17_05/tables/biblio_metadata.html> table and so the report would look just slightly different. Best, ERIC PHETTEPLACE Systems Librarian libraries.cca.edu | vault.cca.edu | 510.594.3660 5212 Broadway, Oakland, CA 94618 1111 8th St., San Francisco, CA 94107 Preferred Pronoun(s): he/him :(){ :|: & };: On Tue, Feb 27, 2018 at 2:03 AM, vanda koha <vandakoha@gmail.com> wrote:
Hi everybody,
We are using 16.11.04 Koha version and I need to have a list of all my Lost documents, so I am running the following report:
SELECT i.itemnumber, b.title, b.author, i.itemcallnumber, i.barcode, v.lib FROM items i LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber) LEFT JOIN authorised_values v ON (i.itemlost=v.authorised_value) WHERE i.itemlost != 0 AND v.category='LOST'
However this is a little incomplete, because I need also the information about the *Edition Statement*, *Publisher Name *and the *Date of Publication *to appear in the results. We use Unimarc and the fields I also need to show up in the results are:
205 a (Edition Statement) 210 c (Publisher Name) 210 d (Date of Publication).
Can anyone help me with this SQL query?
Many thanks in advance
Vanda _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha