Koha reports : List of records without items

This SQL report generates a list of all records that have no items. It has a clickable link to view the record in OPAC and another link to edit the record in staff module.

SELECT b.title AS Title, 
  CONCAT('<a href=\"', IF(CHAR_LENGTH(systempreferences.value), CONCAT('http://', systempreferences.value), ''), '/cgi-bin/koha/opac-         detail.pl?biblionumber=',b.biblionumber,'\">',b.biblionumber,'</a>') AS "View in OPAC",
  CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',b.biblionumber,'\">',b.biblionumber,'</a>') AS Edit  
FROM systempreferences, biblio AS b 
  LEFT JOIN items AS i ON b.biblionumber = i.biblionumber 
WHERE i.itemnumber IS NULL AND systempreferences.variable='OPACBaseURL'

Otuoma Sanya

Otuoma Sanya is a full-time systems librarian, tech enthusiast and writer. His areas of interest are data mining, institutional repositories, library automation and web development using python Django.

Leave a Reply