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?

Handy library manager uses Firebird SQL database engine. There are several tables that define our library database system. We group them into 2 types: data tables (Library, Copies, and Borrowers) and transaction tables (Loans, Reservations). Data tables and transaction tables store unique identification numbers in "ID" data fields. Use those fields to join several tables together and display results for further analysis, summaries, or reports.

LIBRARY, unique identification number for each item is stored in IDNO data field.
LIB_COPIES, unique identification number for a copy is stored in ID data field, copy is linked with the main item data (LIBRARY) through copy IDNO data field.
BORROWERS, unique identification number for each borrower is stored in BOR_IDNO data field.
LOANS, unique identification number for each loan transaction is stored in ID data field, transaction is linked with the item (LIBRARY) through ITEM_IDNO, transaction is linked with copy (LIB_COPIES) through ITEM_ID, and transaction is linked with borrower (BORROWER) through BOR_IDNO.

In some situations you can join several tables to retrieve additional information. For example, the system is used by small elementary school; student's record includes class information. There are many overdue items in the library. A librarian wants to create a report with overdue books by class, so only one report per class is created. The loan transaction is not storing class data. To have access to class information you will have to create temporary table that will include information from loan transactions and borrowers tables.

Using SQL definitions gives you flexibility in finding answers to many questions about your library.
What book category is the most popular in the library?
What is the number of loans by patron type?
What is the number of loans by student class, age?
What is the number of loans by month?
Which book has the largest number of copies?
What is the number of loans by 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.3, review new features

Labels Printing Software for Windows

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?