Basic SQL for Koha Users
Jason Robb & Christopher Brannon
Monday, April 6th, 2020 @ 8:30 PT / 9:30 MT / 10:30 CT / 11:30 ET
What is SQL?
Available resources
Understanding the Schema
Basic Keywords
Getting started
Building a Basic Query
Parameters
Parameters are formatted with the hungry alligators: <<parameter>>
You can define a label for the parameter using the alligators with a pipe: <<Choose Library|branches>>
Parameters can also be selectable by using:
Adding other tables
Example: LEFT JOIN w/USING
SELECT COUNT(s.datetime) AS count
FROM statistics s
LEFT JOIN items i USING (itemnumber)
WHERE s.datetime BETWEEN <<Between (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>> AND i.homebranch = <<Owning Library|branches>> AND s.type IN ('renew')
Example: LEFT JOIN w/ON
SELECT COUNT(s.datetime) AS count
FROM statistics s
LEFT JOIN items i ON (s.itemnumber = i.itemnumber)
WHERE s.datetime BETWEEN <<Between (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>> AND i.homebranch = <<Owning Library|branches>> AND s.type IN ('renew')
Organizing
Example: ORDER BY (Single)
SELECT b.branchcode,b.surname,b.firstname,b.cardnumber,b.categorycode
FROM borrowers b
WHERE b.categorycode='STAFF'
ORDER BY b.branchcode ASC
Example 1: ORDER BY (Multiple)
SELECT b.branchcode,b.surname,b.firstname,b.cardnumber,b.categorycode
FROM borrowers b
WHERE b.categorycode='STAFF'
ORDER BY b.branchcode,b.surname,b.firstname ASC
Example 2: ORDER BY (Multiple)
SELECT b.branchcode,b.surname,b.firstname,b.cardnumber,b.categorycode
FROM borrowers b
WHERE b.categorycode='STAFF'
ORDER BY b.branchcode DESC,b.surname ASC,b.firstname ASC
Useful tactics
Example: BETWEEN
SELECT COUNT(s.datetime) AS count
FROM statistics s
LEFT JOIN items i ON (s.itemnumber = i.itemnumber)
WHERE s.datetime BETWEEN <<Between (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>> AND i.homebranch = <<Owning Library|branches>> AND s.type IN ('renew')
Example: Concat / Concat_WS
SELECT CONCAT_WS(', ',p.surname,p.firstname) AS 'Patron',CONCAT('<a href="http://staff.cin.bywatersolutions.com/cgi-bin/koha/members/memberentry.pl?op=modify&borrowernumber=',p.borrowernumber,'" target="_blank">',p.cardnumber,'</a>') AS 'Library Card'
GROUP BY
Example: Circulation by collection code
SELECT a.lib AS Collection, COUNT(*) AS 'Checkouts and Renewals'
FROM statistics
LEFT JOIN authorised_values a ON (statistics.ccode=a.authorised_value)
WHERE type in ('issue','renew')
AND datetime BETWEEN <<Find stats between|date>> AND <<and|date>>
AND branch = <<Choose Library|branches>>
GROUP BY ccode
HAVING
Examples
Example: Fine free branches
SELECT branchcode, SUM(fine) AS totalfines
FROM issuingrules
GROUP BY branchcode
HAVING totalfines = 0
Unions
Example
SELECT cardnumber, surname, firstname, dateenrolled
FROM borrowers
WHERE dateenrolled > <<Minimum date|date>>
Example
SELECT cardnumber, surname, firstname, dateenrolled
FROM borrowers
WHERE dateenrolled > <<Minimum date|date>>
UNION
SELECT cardnumber, surname, firstname, dateenrolled
FROM deletedborrowers
WHERE dateenrolled > <<Minimum date|date>>
Querying Marc Data (Tag with all subfields)
SELECT ExtractValue(metadata,'//datafield[@tag="952"]/*') AS ITEM
FROM biblio_metadata
WHERE biblionumber=14;
Querying Marc Data (Tag with specific subfield)
SELECT ExtractValue(metadata,'//datafield[@tag="260"]/subfield[@code="b"]') AS PUBLISHER
FROM biblio_metadata
WHERE biblionumber=14;
Querying Marc Data (Specific tag from multiple instances)
SELECT ExtractValue( metadata, '//datafield[@tag="856"][1]/subfield[@code="u"]' ) AS '856$u'
FROM biblio_metadata
WHERE biblionumber=14;
Practicing with SQL