Thanks to Tim Hannah at the New Zealand Ministry of Education, I have got a report that works with the following SQL: SELECT biblio.title, items.itemcallnumber, items.holdingbranch, items.homebranch, items.barcode, count(distinct items.holdingbranch) FROM items LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber WHERE ( (items.holdingbranch = 'WELLINGTON') or (items.holdingbranch = <<Pick your branch|branches>>) ) GROUP BY biblio.title HAVING COUNT(distinct items.holdingbranch)>1 Cheers, Gunilla -----Original Message----- From: koha-bounces@lists.katipo.co.nz [mailto:koha-bounces@lists.katipo.co.nz] On Behalf Of Gunilla Jensen Sent: Monday, 23 September 2013 2:38 p.m. To: koha@lists.katipo.co.nz Subject: [Koha] Reporting help Hello all NIWA Library are combining branch collections around New Zealand into one centralised in Wellington. For deselection purposes I'm trying to create a report, so I can see what titles we have got duplicate items in Wellington and at other branches. However, I'm not having much success with the SQL below. I can't get the AND operator to work (e.g. WHERE holdingbranch = 'WELLINGTON' AND holdingbranch = 'CHRISTCH'). Instead I use IN(X,Y) but this operator uses 'OR' as default so I get all titles held both in Wellington and in Christchurch instead of just the titles that are held in both places. Can anyone help me with this? SELECT biblio.title, items.itemcallnumber, items.holdingbranch, items.homebranch, items.barcode FROM items LEFT JOIN biblio ON items.biblionumber=biblio.biblionumber WHERE holdingbranch IN('WELLINGTON', 'CHRISTCH') ORDER BY biblio.title Thank you, Gunilla Gunilla Jensen | Librarian (Technologies) NIWA -National Institute of Water and Atmospheric Research<http://niwa.co.nz/> Private Bag 19401, Kilbirnie, Wellington, 6241, NZ | 04-3860544 library@niwa.co.nz<mailto:library@niwa.co.nz> -- Please consider the environment before printing this email. NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd. _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha -- Please consider the environment before printing this email. NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd.