Handy Library Manager, How to use Structured Query Language with Handy Library Manager?

Library Software for Windows

Handy Library Manager
for Windows

Library Software / Handy Library Manager
Get Free Trial Buy Now

Help, how to topics > Custom Views > How to use Structured Query Language with Handy Library Manager?

ask a question, report a problem, ...

Views, Statistics
-  save a view
-  views assistant
-  ready-to-use views
-  times checked out
-  copies per title
-  never loaned out items
-  loans by year, month
-  cost of not returned items
-  loans per borrower
-  statistics total purchase cost
-  overdue items report with call, location
>  library SQL sample, join 3 tables
-  overdue items by borrower type
-  overdue item by student class
-  loaned out items by book format
-  yearly circulations by item type

< overdue items report with call, location

overdue items by borrower type >

How to use Structured Query Language with Handy Library Manager?

The Handy Library Manager utilizes the Firebird SQL database engine. Our library database system consists of several tables, which we categorize into two types: data tables (Library, Copies, and Borrowers) and transaction tables (Loans and Reservations). Both data and transaction tables contain unique identification numbers in their respective "ID" fields. These fields can be used to join multiple tables together, enabling further analysis, summaries, or reports.

LIBRARY: The unique identification number for each item is stored in the IDNO field.
LIB_COPIES: The unique identification number for each copy is stored in the ID field. Each copy is linked to its main item in the LIBRARY table through the IDNO field.
BORROWERS: The unique identification number for each borrower is stored in the BOR_IDNO field.
LOANS: The unique identification number for each loan transaction is stored in the ID field. This transaction is linked to the item (LIBRARY) through the ITEM_IDNO field, is associated with a copy (LIB_COPIES) via the ITEM_ID field, and is linked to the borrower (BORROWERS) using the BOR_IDNO field.

In certain situations, you may need to join several tables to retrieve additional information. For example, in a small elementary school using the system, student records include class information. If there are many overdue items in the library, a librarian might want to generate a report of overdue books categorized by class. Since loan transactions do not store class data, you would need to create a temporary table that consolidates information from the Loans and Borrowers tables.

Using SQL definitions provides significant flexibility in finding answers to various questions about your library, such as:
- What book category is the most popular?
- What is the number of loans by patron type?
- What is the number of loans by student class and age?
- What is the number of loans by month?
- Which book has the largest number of copies?
- What is the number of loans per borrower?

The same query you can enter in the following form:
SELECT BORROWERS.NAME, BORROWERS.TYPE, LIBRARY.TITLE, LOAN.RETURNDATE, LOAN.DUEDATE FROM LOAN JOIN BORROWERS ON LOAN.BOR_IDNO=BORROWERS.BOR_IDNO JOIN LIBRARY ON LOAN.ITEM_IDNO=LIBRARY.IDNO
In this query we refer to the table's fields using the table name.

Tables and fields used in the query:

A - Loans table (LOAN), data fields: RETURNDATE, DUEDATE, ITEM_IDNO
B - Borrowers table (BORROWERS), data fields: NAME, TYPE, BOR_IDNO
C - Libary Items table (LIBRARY), data fields: TITLE, IDNO

SELECT B.NAME, B.TYPE, C.TITLE, A.RETURNDATE, A.DUEDATE
this part defines data displayed

FROM LOAN A JOIN BORROWERS B ON A.BOR_IDNO=B.BOR_IDNO JOIN LIBRARY C ON A.ITEM_IDNO=C.IDNO
this part difines what tables are joined and by which data field.

All loan transactions are listed.

To the same SQL definition as above a condition is added. List only items that were not returned.

WHERE A.RETURNDATE IS NULL

handy library sql sample, join 3 tables, a condition is added

Only outstanding loans are listed (not returned items).

To the same SQL definition as above a new condition is added. List only not returned items for borrowers who have Child as a type.

AND B.TYPE='Child'

handy library sql sample, join 3 tables, two conditions are added

Only outstanding loans where borrower's type is equal to Child are listed.

Question: I have a question about sql, how to join 3 tables, I want to link, borrowers, loans, items and display for example 4 fields: PatronName, PatronType, ItemTitle, ReturnDate, DueDate

Handy Library Manager 4.4, review new features

Inquiries from Librarians and Libraries

Library Web Search, review new web search

Library Search 4.0, review new desktop search

Getting started library video lessons

How to include library name on labels

Library, Data Entry Form Designer

Edit item or borrower data in the check-in/check-out window?

Link item lost fine with the PRICE data field?

Handy v3.2 supports letter tabs?

How to predefine library email messages?

How to update library data from the web with ISBN?