select concat(b.title, ' ', ExtractValue(bmd.metadata, '//datafield[@tag="245"]/subfield[@code="b"]')) AS 'title', ExtractValue(bmd.metadata, '//datafield[@tag="100"]/subfield[@code="a"]') AS 'author' , b.copyrightdate, SUBSTR(ExtractValue(bmd.metadata,'//controlfield[@tag="008"]'),8,4) as 'pub year', i.barcode, i.itemnumber, i.location, i.itype, i.copynumber AS 'Copy Number', i.dateaccessioned as "Date Accessioned", i.datelastseen AS "Date Last Seen", i.datelastborrowed as "Date Last Borrowed", i.itemcallnumber AS "Callnumber", i.cn_sort AS "Call Number Sort", i.itemlost AS "Item Lost Status", i.issues as "Checkouts", SUM(IF(s.type = 'localuse', 1, 0)) AS 'Local Uses', s.datetime AS 'Last Local Use', ExtractValue(bmd.metadata, '//datafield[@tag="561"]/subfield[@code="a"]') as 'Named Collection', ExtractValue(bmd.metadata, '//datafield[@tag="250"]/subfield[@code="a"]') as 'Edition Info', ExtractValue(bmd.metadata, '//datafield[@tag="590"]/subfield[@code="a"]') as 'localnote', i.itemnotes, i.itemnotes_nonpublic, ExtractValue(bmd.metadata, '//datafield[@tag="035"]/subfield[@code="a"]') as 'oclc_num 35a', ExtractValue(bmd.metadata, '//controlfield[@tag="001"]') as 'field001' FROM authorised_values v LEFT JOIN items i ON (i.location=v.authorised_value) LEFT JOIN biblio_metadata bmd USING (biblionumber) LEFT JOIN biblioitems bi USING (biblionumber) LEFT JOIN biblio b ON (b.biblionumber = i.biblionumber) LEFT JOIN itemtypes ON (i.itype=itemtypes.itemtype) LEFT JOIN (SELECT * FROM statistics WHERE type = 'localuse') s using (itemnumber) WHERE i.barcode IN ( 32467000528417, 32467001392300, 32467001392318, 32467000526890, 32467001374027, 32467001374035, 32467001374043, 32467001178808, 32467001030256, 32467001015869, 32467000875552, 32467001218984, 32467000671415, 32467000597305, 32467001015851, 32467001015810, 32467000586514, 32467000298201, 32467000538952, 32467000466774, 32467000713043, 32467000191539, 32467000982994, 32467000611437, 32467000608029, 32467000748437, 32467000712318, 32467000509052, 32467000704687, 32467000647811, 32467000532146, 32467000758246, 32467000684145, 32467000362593, 32467001883571, 32467000290950, 32467002315557, 32467001165243, 32467001175028, 32467000266125, 32467000607088, 32467002375007, 32467001512790, 32467001043283, 32467000724644, 32467001075665, 32467000210925, 32467000563711, 32467000526502, 32467001016198, 32467002532789, 32467000803927, 32467000699762, 32467002169319, 32467000552391, 32467000682107, 32467000736143, 32467000737224, 32467000478431, 32467001219206, 32467000245111, 32467000795214, 32467000657711, 32467000496763, 32467000489909, 32467000239700, 32467000276900, 32467000292329, 32467000468325, 32467000354434, 32467000465586, 32467000732514, 32467000536519, 32467001711384, 32467001016164, 32467001235350, 32467001016016, 32467001823858, 32467001824146, 32467000765803, 32467000886260, 32467000459860, 32467000306236, 32467000459886, 32467001626913, 32467001670481, 32467001619470, 32467001626921, 32467000600893, 32467001647570, 32467001647588, 32467001342156, 32467001645228, 32467001670002, 32467000576929, 32467001643629, 32467001626939, 32467000702772, 32467000461437, 32467001041071, 32467001060345, 32467000948755, 32467001460495, 32467000485048, 32467000970346, 32467000812464, 32467000210016, 32467000210172, 32467000210339, 32467000210495, 32467000210008, 32467001702151, 32467001718579, 32467000228430, 32467000228422, 32467000228448, 32467000766900, 32467001336554, 32467001044091, 32467001142002, 32467000871320, 32467000987894, 32467000210156, 32467000772965, 32467000209992, 32467001064933, 32467001193054, 32467000751738, 32467001073702, 32467001015828, 32467000505092, 32467000584493, 32467000942915, 32467000519168, 32467000959406, 32467001000606, 32467002375106, 32467000210115, 32467000985526, 32467001227647, 32467001351694, 32467001151722, 32467001065468, 32467001064941, 32467000931314, 32467000658081, 32467001745424, 32467001061665, 32467000701485, 32467000708092, 32467000800915, 32467000889280, 32467001338766, 32467000321607, 32467000315468, 32467001015844, 32467000311343, 32467000239544, 32467000977200, 32467000587835, 32467001255077, 32467001015836, 32467000768419, 32467000657646, 32467000865769, 32467000666969, 32467001015943, 32467000923741, 32467000723299, 32467002375031, 32467002403437, 32467001714222, 32467002209420, 32467000239619, 32467001250722, 32467000830318, 32467000210438, 32467001059578, 32467000238132, 32467000629595, 32467000209968, 32467001065443, 32467001008955, 32467000732282, 32467000598436, 32467000841463, 32467001937294, 32467002369893, 32467001724627, 32467002143991, 32467001345548, 32467001892184, 32467000656036, 32467001701872, 32467001218240, 32467001734931, 32467000573405, 32467000780547, 32467001058968, 32467001015448, 32467001351843, 32467000592975, 32467001905721, 32467000823248, 32467001110843, 32467001158594, 32467002346230, 32467002363631, 32467000362619, 32467000600810, 32467001439770, 32467001439788, 32467000811870, 32467001251225, 32467001252322, 32467001149452, 32467000240492, 32467000280621, 32467002238379, 32467000741036, 32467000268212, 32467000667942, 32467000308497, 32467000584600, 32467002432238, 32467001359713, 32467000308430, 32467000257611, 32467000799968, 32467001164030, 32467000289390, 32467000308471, 32467001232134, 32467000308489, 32467001403768, 32467002238056, 32467000927395, 32467000606643, 32467000053416, 32467000597685, 32467000053390, 32467000274244, 32467000927536, 32467000965882, 32467001255739, 32467000308448, 32467000735640, 32467000484447, 32467000484447, 32467001344723, 32467000157837, 32467000611114, 32467000289655, 32467000327224, 32467000352545, 32467001251860, 32467000810716, 32467000285315, 32467001254682, 32467000308505, 32467000309347, 32467000347909, 32467000731722, 32467000520273, 32467000308513, 32467000240138, 32467000538218, 32467000484041, 32467001664963, 32467001664971, 32467000700453, 32467001692584, 32467000516594, 32467000792120, 32467001234874, 32467000847106, 32467000053440, 32467000053465, 32467002238403, 32467000718331, 32467001218844, 32467000289770, 32467001187783, 32467000247257, 32467000538861, 32467000314453, 32467000284813, 32467001172991, 32467001345530, 32467002298001, 32467002298019, 32467001229635, 32467001754632, 32467001759052, 32467002237967, 32467000308646, 32467001787624, 32467000599806, 32467000769300, 32467000226640, 32467000314438, 32467000524747, 3999900000001 ) GROUP BY i.itemnumber ORDER BY i.cn_sort, s.datetime DESC