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.
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@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@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
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@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@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@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
Thank you so much, it is just what I was after! I'll be happy to post it to the wiki too. :-) Rachel Hollis, librarian Stevens-Henager College, Boise & Nampa -----Original Message----- From: Nicole Engard [mailto:nengard@gmail.com] Sent: Tuesday, March 13, 2012 2:15 PM To: Rachel Hollis Cc: koha@lists.katipo.co.nz Subject: Re: [Koha] SQL statement 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' ________________________________ 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.
participants (2)
-
Nicole Engard -
Rachel Hollis