Dear Caroline, Great it works. May I know where I can find a list of dictionary or fields for the SQL? Thanks & regards, *Peter Lau* Librarian Tel: (852) 3977 9868 | Email: Peter.Lau@yccece.edu.hk | Fax: (852) 23384320 [image: https://docs.google.com/uc?export=download&id=1wiYZQsqhNTJZjyNbLJx-6ODZyoYE5NNu&revid=0BzpPRj4UE0XeT1R2WUt2dnhmTFBBeEpVR3dqcU5QbnF6NVg4PQ] 2 Tin Wan Hill Road, Tin Wan, Aberdeen *YCCECE, Here We Grow Our Future | *www.yccece.edu.hk *From:* Caroline Cyr La Rose [mailto:caroline.cyr-la-rose@inlibro.com] *Sent:* Sunday, 20 January 2019 3:49 AM *To:* koha list <koha@lists.katipo.co.nz>; Peter Lau < peter.lau@yccece.edu.hk> *Subject:* Re: [Koha] Course Reserve information from customized report Hi Peter, Try this SELECT courses.course_number AS "Course code", courses.course_name AS "Course", CONCAT(borrowers.surname, ', ', borrowers.firstname, ' (', borrowers.cardnumber, ')') AS "Course teacher", biblio.title AS "Title", biblio.author AS "Author", biblioitems.isbn AS "ISBN", items.itype AS "Item type", items.itemcallnumber AS "Item callnumber", items.barcode AS "Item barcode" FROM course_items LEFT JOIN items USING (itemnumber) LEFT JOIN biblio USING (biblionumber) LEFT JOIN biblioitems USING (biblionumber) LEFT JOIN course_reserves USING (ci_id) LEFT JOIN courses USING (course_id) LEFT JOIN course_instructors USING (course_id) LEFT JOIN borrowers USING (borrowernumber) Adapted from https://wiki.koha-community.org/wiki/SQL_Reports_Library#All_course_reserve_... -- Caroline Cyr La Rose, M.L.I.S Librarian | Product Manager, inLibro Le 19 janvier 2019 à 01:19:40, Peter Lau (peter.lau@yccece.edu.hk) a écrit: I would like to draw a list of all items cataloged under Course Reserve including the Course Code, Course teacher (if input), Bib title, Bib author, ISBN etc. I don't know how to draw them easily from the report function. Thanks and regards, *Peter Lau* Librarian Tel: (852) 3977 9868 | Email: peter.lau@yccece.edu.hk <library@yccece.edu.hk> | Fax: (852) 23384320 2 Tin Wan Hill Road, Tin Wan, Aberdeen *YCCECE, Here We Grow Our Future | *www.yccece.edu.hk _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha

Dear Indranil, Thanks for your info. Thanks & regards, Peter Lau Librarian Tel: (852) 3977 9868 | Email: Peter.Lau@yccece.edu.hk | Fax: (852) 23384320 2 Tin Wan Hill Road, Tin Wan, Aberdeen YCCECE, Here We Grow Our Future | www.yccece.edu.hk -----Original Message----- From: Indranil Das Gupta [mailto:indradg@gmail.com] Sent: Monday, 21 January 2019 9:33 AM To: Peter Lau <peter.lau@yccece.edu.hk> Cc: koha list <koha@lists.katipo.co.nz> Subject: Re: [Koha] Course Reserve information from customized report Hi Peter, Koha's database schema is documented here version wise - http://schema.koha-community.org/ hope this helps Indranil Das Gupta L2C2 Technologies Phone : +91-98300-20971 WWW : http://www.l2c2.co.in Blog : http://blog.l2c2.co.in IRC : indradg on irc://irc.freenode.net Twitter : indradg On Mon, Jan 21, 2019 at 6:57 AM Peter Lau <peter.lau@yccece.edu.hk> wrote:

Dear Caroline,

Great it works.

May I know where I can find a list of dictionary or fields for the SQL?

Thanks & regards,

*Peter Lau*

Librarian

Tel: (852) 3977 9868 | Email: Peter.Lau@yccece.edu.hk | Fax: (852) 23384320

[image: https://docs.google.com/uc?export=download&id=1wiYZQsqhNTJZjyNbLJx-6OD ZyoYE5NNu&revid=0BzpPRj4UE0XeT1R2WUt2dnhmTFBBeEpVR3dqcU5QbnF6NVg4PQ]

2 Tin Wan Hill Road, Tin Wan, Aberdeen

*YCCECE, Here We Grow Our Future | *www.yccece.edu.hk

*From:* Caroline Cyr La Rose [mailto:caroline.cyr-la-rose@inlibro.com] *Sent:* Sunday, 20 January 2019 3:49 AM *To:* koha list <koha@lists.katipo.co.nz>; Peter Lau < peter.lau@yccece.edu.hk> *Subject:* Re: [Koha] Course Reserve information from customized report

Hi Peter,

Try this

SELECT

courses.course_number AS "Course code",

courses.course_name AS "Course",

CONCAT(borrowers.surname, ', ', borrowers.firstname, ' (', borrowers.cardnumber, ')') AS "Course teacher",

biblio.title AS "Title",

biblio.author AS "Author",

biblioitems.isbn AS "ISBN",

items.itype AS "Item type",

items.itemcallnumber AS "Item callnumber",

items.barcode AS "Item barcode"

FROM course_items

LEFT JOIN items USING (itemnumber)

LEFT JOIN biblio USING (biblionumber)

LEFT JOIN biblioitems USING (biblionumber)

LEFT JOIN course_reserves USING (ci_id)

LEFT JOIN courses USING (course_id)

LEFT JOIN course_instructors USING (course_id)

LEFT JOIN borrowers USING (borrowernumber)

Adapted from https://wiki.koha-community.org/wiki/SQL_Reports_Library#All_course_re serve_items

-- Caroline Cyr La Rose, M.L.I.S

Librarian | Product Manager, inLibro

Le 19 janvier 2019 à 01:19:40, Peter Lau (peter.lau@yccece.edu.hk) a écrit:

I would like to draw a list of all items cataloged under Course Reserve including the Course Code, Course teacher (if input), Bib title, Bib author, ISBN etc.

I don't know how to draw them easily from the report function.

Thanks and regards,

*Peter Lau*

Librarian

Tel: (852) 3977 9868 | Email: peter.lau@yccece.edu.hk <library@yccece.edu.hk> | Fax: (852) 23384320

2 Tin Wan Hill Road, Tin Wan, Aberdeen

*YCCECE, Here We Grow Our Future | *www.yccece.edu.hk _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha