Gallringslista Bibliotek Mellansjö

SELECT biblio.author AS Författare,
	   CONCAT('<a href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'\" target="_blank">', biblio.title, '</a>' ) AS Titel,
       items.itemcallnumber AS Hyllsignum,
       items.ccode AS Avdelning,
       itemtypes.description AS Exemplartyp,
	   avplac.lib AS Placering,
       items.barcode AS Streckkod,
       items.datelastborrowed AS 'Senast lånad',
       items.issues AS 'Antal lån',
       (SELECT GROUP_CONCAT( ItemInnerDets.ItemDetails ORDER BY ItemInnerDets.ItemDetails ASC SEPARATOR '</br>')
            FROM
           (SELECT itemsInner.biblionumber, branches.branchname, itemsInner.barcode,
                   CONCAT(branches.branchname) AS ItemDetails 
            FROM items itemsInner 
			LEFT JOIN branches ON (itemsInner.homebranch=branches.branchcode)
			) ItemInnerDets
         WHERE ItemInnerDets.biblionumber = items.biblionumber AND  ItemInnerDets.barcode <> items.barcode  ORDER BY ItemInnerDets.ItemDetails  
         )  AS 'Finns även vid'
	   
FROM items 
LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber) 
LEFT JOIN authorised_values avplac ON (items.location=avplac.authorised_value)            
LEFT JOIN itemtypes ON (items.itype=itemtypes.itemtype)            
 WHERE items.homebranch = <<Bibliotek|branches>> 
 AND items.location = <<Placering|LOC>> 
 AND items.ccode = <<Avdelning (barn/vuxen/magasin)|ccode>>
 AND items.itemcallnumber LIKE <<Hyllsignum. <br>Trunkera med %. <br>För att få med alla hyllsignum, skriv bara %>>
 AND (YEAR(NOW())-YEAR(items.datelastborrowed) > <<Antal år sedan senaste utlån>>  OR items.datelastborrowed IS NULL)
 AND YEAR(NOW())-YEAR(items.dateaccessioned) > <<Antal år sedan förvärv>>
 AND items.itemnumber NOT IN (SELECT issues.itemnumber FROM issues) 

ORDER BY itemcallnumber, author, title