Have you tried changing the INNER JOIN to a LEFT JOIN? You'll probably have to make some adjustments elsewhere in the code to handle null BIB_ID values, but at least I believe that should get the necessary details out of the database for you.
From: Osullivan L. [mailto:L.Osullivan@...]
Sent: Monday, July 26, 2010 1:45 PM
Subject: [VuFind-Tech] Fines
Swansea University uses fines for not item related material e.g. Inter Library Loans which do not show up using the standard Voyager driver sql select statement:
$sql = "SELECT unique FINE_FEE_TYPE.FINE_FEE_DESC, FINE_FEE.FINE_FEE_AMOUNT, FINE_FEE.FINE_FEE_BALANCE, FINE_FEE.ORIG_CHARGE_DATE, FINE_FEE.DUE_DATE, BIB_ITEM.BIB_ID " .
"FROM (($this->dbName.FINE_FEE INNER JOIN $this->dbName.FINE_FEE_TYPE ON FINE_FEE.FINE_FEE_TYPE = FINE_FEE_TYPE.FINE_FEE_TYPE) INNER JOIN $this->dbName.PATRON ON FINE_FEE.PATRON_ID = PATRON.PATRON_ID) INNER JOIN $this->dbName.BIB_ITEM ON FINE_FEE.ITEM_ID = BIB_ITEM.ITEM_ID ".
"WHERE PATRON.PATRON_ID=:id AND FINE_FEE.FINE_FEE_BALANCE > 0";
The problem is that as the FINE_FEE>.ITEM_ID field is '0', no match is made in the BIB_ITEM table and the fine is not selected.
Does anyone have any ideas for a work around? I could perform a separate select statement for BIB_IDs (if Fine_fee.item_id == 0) but that seems overkill.
Library Systems Officer - Virtual Academic Library
South West Wales Higher Education Partnership (SWWHEP)
Tel: 01792 602772
Ffôn: 01792 602772
Check out the new SWWHEP Online GreenGuide at: