[Koha] Report filtered by item type fails

[Liz Rea]

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 <liz at 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 at 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