[Koha] SQL statement

[Nicole Engard]

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 <nengard at gmail.com> 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
> <Rachel.Hollis at stevenshenager.edu> 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
>> Koha at lists.katipo.co.nz
>> http://lists.katipo.co.nz/mailman/listinfo/koha