Dear colleague I assume that data appears in the " Additional attributes and identifiers" part of a borrower record. If that's the case, you likely have patron attributes set up for that information (Department, Student Roll number, blood group, etc.).
We have some patron attributes set up to track what "organisation" a borrower is from and what "role" they exercise in that organisation. These are recorded in the borrower_attributes table, with a unique code for each type of data you're using them for (in our case the codes are "ORG" and "ROLE" and they map to specific lists of Authorised Values). The following report gives us a list of borrowers, and for each one we get borrower number, surname, firstname, description of the borrower category, expiry date, the organisation the borrower belongs to and the role they exercise. You might want to try something similar to this (please note that, because we want information for 2 different patron attributes, we call up the borrower_attributes table under 2 separate aliases so that we can ask for borrower_attribute.attribute by different borrower_attribute.code. If you want to pull Department, Student Roll number and blood group (and they are all stored in borrower_attributes) you'll likely need 3 aliases, each asking for attributes with a different code. SELECT b.borrowernumber, b.surname, b.firstname, c.description as "BORROWER TYPE", b.dateexpiry as "DATE EXPIRY", ba1.attribute as "ORG", ba2.attribute as "ROLE" FROM borrowers b LEFT JOIN borrower_attributes ba1 on ba1.borrowernumber = b.borrowernumber and ba1.code = "ORG" LEFT JOIN borrower_attributes ba2 on ba2.borrowernumber = b.borrowernumber and ba2.code = "ROLE" LEFT JOIN categories c on c.categorycode = b.categorycode WHERE b.categorycode = <<Borrower category?|categorycode>> order by b.surname, b.firstname I hope this helps! With kind regards from the Dalton McCaughey Library Team Carlos Lopez Dalton McCaughey Library | 29 College Crescent, Parkville, VICTORIA 3052 Ph: 03 9340 8888 ext.1 | libr...@dml.vic.edu.au | library.dmlibrary.org.au -----Original Message----- From: Koha <koha-boun...@lists.katipo.co.nz> On Behalf Of MASTeR Library Sent: Thursday, 16 March 2023 3:59 PM To: koha <koha@lists.katipo.co.nz>; koha-requ...@lists.katipo.co.nz; koha-de...@lists.koha-community.org Subject: [Koha] How to get sql report on patron additional attributes. CAUTION: This email originated from outside of the organization. Do not click links or open attachments unless you recognize the sender and know the content is safe. Dear Team, how to get the sql report on Patron additional attributes for example Department, Student Roll number, blood group and etc? Sample SQL Code. SELECT ROW_NUMBER() OVER (ORDER BY LPAD(borrowers.cardnumber,100,' ') ASC) AS 'S.No', borrowers.cardnumber AS "Lib Membership No",borrowers.borrowernumber,borrowers.surname AS " Name",borrowers.title,borrowers.sex,borrowers.dateofbirth AS " Date of Birth",borrowers.contactname AS "Guardian Name", borrowers.relationship, borrowers.phone,borrowers.email,borrowers.address,borrowers.categorycode,borrowers.dateenrolled,borrowers.dateexpiry,borrowers.userid FROM borrowers WHERE branchcode=<<Enter patrons library|branches>> AND categorycode LIKE <<Select User category|categorycode>> -- Thanking you.. Librarian, MAMCET <https://urldefense.com/v3/__http://mamcet.com/__;!!DVrgiXjqvl2yLjg!ed7jDm8AzcAFRxKjA0LeIvn7Ckox8by06f7g1LJQMXGUGZCEqGNXh4tYW-oSkiNcgqX-v_-EuT8g2McSUG_-mQatGQRK-dk$ > Trichy-Chennai Trunk Road, Siruganur, Tiruchirappalli-621 105. _______________________________________________ Koha mailing list https://urldefense.com/v3/__http://koha-community.org__;!!DVrgiXjqvl2yLjg!ed7jDm8AzcAFRxKjA0LeIvn7Ckox8by06f7g1LJQMXGUGZCEqGNXh4tYW-oSkiNcgqX-v_-EuT8g2McSUG_-mQatRcrByRU$ Koha@lists.katipo.co.nz Unsubscribe: https://urldefense.com/v3/__https://lists.katipo.co.nz/mailman/listinfo/koha__;!!DVrgiXjqvl2yLjg!ed7jDm8AzcAFRxKjA0LeIvn7Ckox8by06f7g1LJQMXGUGZCEqGNXh4tYW-oSkiNcgqX-v_-EuT8g2McSUG_-mQatNc7yT94$ _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
