Give this a go: SELECT biblio.biblionumber,biblio.title,biblio.notes,biblioitems.publishercode,biblioitems.itemtype FROM biblioitems LEFT JOIN items USING (biblionumber) LEFT JOIN biblio USING (biblionumber) WHERE biblioitems.itemtype='CR'
I think your problem is that you are linking your tables using biblioitemnumber instead of biblionumber. I hope this helps, let us know if it doesn't! Cheers, Liz On 13/11/14 19:25, Elaine Bradtke wrote: > What happens if you try SELECT itemtype, biblionumber FROM biblioitems > WHERE itemtype = 'CR' - are your results any different? > > I get a list of biblionumbers with itemtype CR - the correct number of > them. (848) > > But when I run this: > > SELECT > > biblio.biblionumber,biblio.title,biblio.notes,biblioitems.publishercode,itemtype > FROM items LEFT JOIN biblioitems on > (items.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN biblio on > (biblioitems.biblionumber=biblio.biblionumber) WHERE itemtype='CR' > > I only get six - all of these records have attached items. Probably the > only six CR records with attached items. > I get the same six records when I use biblioitems.itemtype instead: > > SELECT > > biblio.biblionumber,biblio.title,biblio.notes,biblioitems.publishercode,biblioitems.itemtype > FROM items LEFT JOIN biblioitems on > (items.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN biblio on > (biblioitems.biblionumber=biblio.biblionumber) WHERE > biblioitems.itemtype='CR' > > > On Thu, Nov 13, 2014 at 2:44 AM, Liz Rea <l...@catalyst.net.nz> wrote: > >> Hi Elaine, >> >> What happens if you try SELECT itemtype, biblionumber FROM biblioitems >> WHERE itemtype = 'CR' - are your results any different? >> >> Cheers, >> Liz >> On 13/11/14 14:43, Elaine Bradtke wrote: >>> Koha 3.16 >>> According to my statistical report, we have 848 biblios that are >>> continuing resources (BIBITEMTYPE CR) >>> But when I try to build a report that limits the output to these records >> I >>> get only a handful of records (these uniquely have items attached, 99% of >>> our CR records do not, so this information usually only appears in the >>> 942c). >>> >>> If I run the following report it doesn't list any entries with CR. >>> SELECT biblionumber, ExtractValue(marcxml, >>> '//datafield[@tag="942"]/subfield[@code="c"]') AS comp FROM >>> biblioitems >>> >>> But I can search and retrieve individual records, and they do indeed have >>> CR in the 942c. >>> >>> When I run an advanced search and limit it to this item type, the search >>> results are exactly what I expect to see. >>> >>> I can't figure out how it will count them in one instance, but not list >>> them in another; find them in the advanced search, but not when I query >>> the database with a report. . . >>> >>> We've just moved our server over to a new virtual machine and a different >>> version of Linux. . . Is the above a symptom of indexing problems or some >>> other technical glitch? >>> >>> >> -- >> -- >> Liz Rea >> Catalyst.Net Limited >> Level 6, Catalyst House, >> 150 Willis Street, Wellington. >> P.O Box 11053, Manners Street, >> Wellington 6142 >> >> GPG: B149 A443 6B01 7386 C2C7 F481 B6c2 A49D 3726 38B7 >> >> _______________________________________________ >> Koha mailing list http://koha-community.org >> Koha@lists.katipo.co.nz >> http://lists.katipo.co.nz/mailman/listinfo/koha >> > > -- -- Liz Rea Catalyst.Net Limited Level 6, Catalyst House, 150 Willis Street, Wellington. P.O Box 11053, Manners Street, Wellington 6142 GPG: B149 A443 6B01 7386 C2C7 F481 B6c2 A49D 3726 38B7 _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
