Handy Library Manager  for Windows


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


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.


handy library sql sample, join 3 tables

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.


Questions: 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



Copyright © 2019 · All Rights Reserved · PrimaSoft PC