That didn't work right! try this instead:
SELECT concat(b.title, ' ', ExtractValue(( SELECT marcxml FROM biblioitems b2 WHERE b.biblionumber = b2.biblionumber), '//datafield[@tag="245"]/subfield[@code="b"]')) AS title, b.author, i.itemcallnumber FROM biblio b LEFT JOIN items i ON (i.biblionumber=b.biblionumber) where i.homebranch=<<homebranch|branches>> AND i.location='LIB' On Tue, Mar 13, 2012 at 4:13 PM, Nicole Engard <[email protected]> wrote: > I think this is all you need: > > SELECT concat(b.title, ' ', ExtractValue(( SELECT marcxml FROM > biblioitems b2 WHERE b.biblionumber = b2.biblionumber), > '//datafield[@tag="245"]/subfield[@code="b"]')) AS title, b.author, > i.itemcallnumber FROM biblio b LEFT JOIN items i ON > (i.biblionumber=b.biblionumber) and > i.homebranch=<<homebranch|branches>> AND i.location='LIB' > > > Nicole > > On Tue, Mar 13, 2012 at 4:00 PM, Rachel Hollis > <[email protected]> wrote: >> Greetings friends, I have two SQL statements that I would like to combine >> but I don't know SQL well enough to do it myself. Could someone help me? >> >> I want to get a list of titles (245a and 245b), authors, and call number >> along with home-branch and library location. >> Below is what I have, probably from the wiki: >> SELECT concat(b.title, ' ', ExtractValue(( SELECT marcxml FROM biblioitems >> b2 WHERE b.biblionumber = b2.biblionumber), >> '//datafield[@tag="245"]/subfield[@code="b"]')) AS title, b.author, >> i.itemcallnumber FROM biblio b LEFT JOIN items i ON >> (i.biblionumber=b.biblionumber) >> >> And from another report: >> WHERE items.homebranch=<<homebranch|branches>> AND items.location='LIB' >> >> Copy and paste didn't work for me. ;-) >> >> Rachel Hollis, librarian >> Stevens-Henager College, Boise & Nampa Idaho >> >> ________________________________ >> >> This message is for the designated recipient only and may contain >> privileged, proprietary, or otherwise private information. If you have >> received it in error, please notify the sender immediately and delete the >> original. Any other use of the email by you is prohibited. >> _______________________________________________ >> Koha mailing list http://koha-community.org >> [email protected] >> http://lists.katipo.co.nz/mailman/listinfo/koha _______________________________________________ Koha mailing list http://koha-community.org [email protected] http://lists.katipo.co.nz/mailman/listinfo/koha
