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.
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
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.
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