Fourth Report Challenge: A client wants to put together a report to show cardnumber, firstname, second name, current issues
These notes are intended to be used to help answer the associated Report Challenge that was released this week.
Take a look at the question below and think about how you would approach answering it:
A client wants to put together a report to show cardnumber, firstname, second name and current issues?
Key points in this challenge:
Using the JOIN clause
What a primary key does
How to view a diagram of the Koha table schema
Switching the primary table being pulled back in the report
This report request can be answered by building on the reports created in previous challenges. To view the content of the original report, click the dropdown option below:
Do you want to view the original report content?
SELECT borrowers.firstname, borrowers.surname, borrowers.cardnumber
FROM borrowers
Similar to earlier lessons, this begins by identifying the tables and fields that need to be added to the report.
This report needs two tables, one of which appears in the original report challenge. The first step is identifying the other table to be added to the report and the associated fields from the second table that should be added to the report.
The tables to link
Previous challenges have included details for how to navigate to the Koha database schema.
For more details on how to navigate to the Koha database, please check our Help Centre.
The schema also includes a diagram of the database schema, which can be intimidating when first looked at. This can be a good tool to identify how tables can be joined in reports.
The below example uses the borrowers table to demonstrate how to view and use the diagram within the Koha database schema, but the steps can be applied to any table within the schema.
This is what the overall page for the borrowers table appears as in the Koha database schema.
If you scroll down the page to the section titled Relationships, it will show a diagram of how different tables are related to the borrowers table.
If we look at the diagram of the database that shows the table, we can see it links out to multiple other tables.
The borrowers table will appear centrally in the page:
There will be many tables linked off this central table. Examining the surrounding tables will help you learn the other significant tables that can be drawn on.
Identifying the correct table can be a matter of trial and error. In this case, we can see there are tables called Issues and old_issues.
Which table looks like it contains information we need for this report?
There are several tables that relate to checkouts, for more details on these options, please submit a ticket or check our Help Centre.Which table should be used for this table
In this case, it is the issues table as this is the table that contains details about the current checkouts on the system.
Please note, the issues table contains only information about items that are currently checked out. It will not contain any information about items that have been checked out and checked back in.
Fields
We need to identify the fields from the issues table to be included in the report.
To look at the issues table in detail, click on the issues table within the diagram of the Koha database schema we were looking at above.
This will open the page with full details of the issues table.
Or use previously provided instructions for search the database schema to find the issues table.
For more details on searching for a table, please check our Help Centre.
In this case, we want to include a field that will have content for every item that is checked out. This field will vary from report to report and there may be more than on field that can fulfil the need to have an entry for every facet.
In this case, I would advise using the itemnumber field because every item record on the system will have an item number so no cell in the issues table will be empty for itemnumber.
How to link the tables
Once the tables and required fields are identified, we need to identify a common field between tables so they can be reported on at once.
Tables are linked through the primary key. This is a field for a unique identifier common between tables.
You will be able to see the primary key for a table in the Indexes section of the Koha database schema.
Or in the Koha database schema.
JOIN clause
There are a few JOINS that can be used to link tables together. We will look at three main ones.
Left Join - The LEFT JOIN returns all rows from the first table mentioned in the report and only return matched rows from the second table mentioned in the report.
Right Join - The RIGHT JOIN returns all rows from the second table mentioned in the report and only return matched rows in the first table mentioned in the report.
Inner Join - Default - The INNER JOIN returns rows in all tables if they are matching rows.
To see how the final report would work with different JOINS, click here. Be aware this will show the format of the final report
If we were to use the Left Join for this report:
SELECT borrowers.firstname, borrowers.surname, borrowers.cardnumber, issues.itemnumber
FROM issues
LEFT JOIN borrowers ON issues.borrowernumber = borrowers.borrowernumber
LIMIT 10
This will bring back everything from the issues table and only matching results from the borrowers table. So if there is content in the borrowers table that doesn't match the content in the issues table, it won't be brought back.
If we were to use the Right JOIN for this report:
SELECT borrowers.firstname, borrowers.surname, borrowers.cardnumber, issues.itemnumber
FROM borrowers
RIGHT JOIN issues ON borrowers.borrowernumber = issues.borrowernumber
LIMIT 10
This would bring back everything from the borrowers table and only matching results from the issues table.
Which JOIN does this report use?
This report will use the INNER JOIN.
The INNER JOIN is the default join that Koha uses if no JOIN is defined. So if a report is created only using the word JOIN, then Koha will assume the JOIN is an INNER JOIN.
We want to use the INNER JOIN so that we will see the content from both the borrowers and the issues table, but to do this we need to use the issues table as the first table called on in the report.
We will look at this formatting in detail below.
Formatting the report to include the JOIN clause
We are working on how to incorporate the new table and the JOIN command.
The standard content of SELECT and FROM should be updated first.
Any additional functions to change how and what are returned should be included below the FROM statement.
The table after the FROM statement
The format of the original report includes borrowers as the table returned after the FROM statement. To use the INNER JOIN we are going to replace this table with the issues table.
The report should now look like this:
SELECT borrowers.firstname, borrowers.surname, borrowers.cardnumber, issues.itemnumber
FROM issues
LIMIT 10
Adding in the new fields.
We have identified the issues table as the other table to include in this report.
The field to be included is the issues.itemnumber field.
To include the new field, add it to the end of the fields already defined in the report.
The report now looks like:
SELECT borrowers.firstname, borrowers.surname, borrowers.cardnumber, issues.itemnumber
FROM issues
LIMIT 10
Running the report in this format will cause an error as the table to pull the borrowers fields from has not been defined under the FROM statement. This is where the JOIN statement comes in.
Adding in the new table
The JOIN statement should be built under the FROM issues.
The JOIN statement goes in first.
The report now looks like this:
SELECT borrowers.firstname, borrowers.surname, borrowers.cardnumber, issues.itemnumber
FROM issues
JOIN
LIMIT 10
The next entry needs to be the table not already defined with the FROM statement. In this case, that is the borrowers table.
The report now looks like this:
SELECT borrowers.firstname, borrowers.surname, borrowers.cardnumber, issues.itemnumber
FROM issues
JOIN borrowers
LIMIT 10
The next step is to define how the issues table can be linked to the borrowers table. This is where the primary key common between tables comes in.
The next step is to add in ON and then the field and table from the other table.
The report now looks like this:
SELECT borrowers.firstname, borrowers.surname, borrowers.cardnumber, issues.itemnumber
FROM issues
JOIN borrowers ON issues.borrowernumber
LIMIT 10
The next step is to define what field in the issues table matches the content of the borrowers table.
The report now looks like this:
SELECT borrowers.firstname, borrowers.surname, borrowers.cardnumber, issues.itemnumber
FROM issues
JOIN borrowers ON issues.borrowernumber = borrowers.borrowernumber
LIMIT 10
Final Report Format
SELECT borrowers.firstname, borrowers.surname, borrowers.cardnumber, issues.itemnumber
FROM issues
JOIN borrowers ON issues.borrowernumber = borrowers.borrowernumber
LIMIT 10