On 30 March 2011 07:49, Kathy Rippel <kdr@ckls.org> wrote:
I'm trying to build a simple weeding tool for one of my libraries and seem to be missing something. I am getting results where the Total_Circ is 2 or more!! The rest looks great, the results are just not narrow enough for this case.
Context may be slightly different because we are on LLEK, but I believe the problem is with the SQL at WHERE items.homebranch='LX' AND 'Total_Circ'<2
We want everything where the Total Circ is less than two, obviously!
Yes, the problem is by making it 'Total_Circ' you have made it a literal. So it is not a column (or alias) anymore. I would do this with a subquery So like this select CONCAT( '<a href=\"/cgi-bin/koha/cataloguing/additem.pl?biblionumber=',biblio.biblionumber, '\">',countitems.barcode,'</a>' ) AS barcode_href, itemcallnumber, author, title, issues, renewals, total_circ FROM (select (IFNULL(items.issues,0)+IFNULL(items.renewals,0)) AS total_circ,itemnumber,barcode,issues,renewals,biblioitemnumber,itemcallnumber,homebranch FROM items) AS countitems LEFT JOIN biblioitems on (countitems.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber) WHERE total_circ < 2 AND homebranch = 'LX' ORDER BY itemcallnumber,author,title Chris