[Koha] Catalogue - SQL Report Help

[SATISH]

Hi,

I found one solution, but it is not through Koha.

I exported unique records generated by both the item type BB and BK and put
it in a single excel file and applied conditional formating and applied
filter ( to ISBN column, since it is unique) by colour and found the
duplicate biblionumber/titles generated between BB and BK (not among BB or
BK) and removed manually. But, still I expect, automated software to
perform this using sql.

When fetching unique records from particular item type is possible means,
merging both the items types results, creating a view for that and
eliminating duplicate records should also be possible using sql. But I am
not good in mysql.

Since my library requirement is to maintain "book" and "book bank" as
separate item types for accommodating more than one circulation rules and
also facilitate searching with-in particular item type.

I request this forum to help me for doing this within koha using sql not
manually (using excel) please or someone already did similar exercise
please help me.

Hoping to get some solution.

With thanks.

Satish MV
Librarian
Govt. Engineering College, Hassan.
Karnataka.


On Fri, Feb 27, 2015 at 11:06 AM, SATISH <lis4satish at gmail.com> wrote:

> Thank you Paul, I made corrections, but the purpose is not solved.
> By using "OR" it populates duplicate record.
>
> By maintaining only one kind of item type, I can not make varied
> circulation policies (but can generate unique records report for books),
> and in this case, for the same user I have to provide two different kind of
> ID cards for issue books from BK and BB, since no provision to create
> different circulation policy.
>
> And if I make two kinds of Item Type, then unique report won't come ? (can
> make varied circulation policies, that is, by using single ID, I can issue
> books of different item types).
>
> Is there any way to generate a report either separately for each item type
> or  as indicated by you using "OR" between BK/BB and then apply some query
> to eliminate duplicates within Koha.
>
> or any other way, koha team could help me ??
>
> Hoping to get some solution.
>
>
> Thank You
> Satish MV
> Librarian
> Govt. Engineering College, Hassan.
> Karnataka.
>
>
>
> On Thu, Feb 26, 2015 at 8:04 PM, Paul A <paul.a at navalmarinearchive.com>
> wrote:
>
>> At 11:00 AM 2/26/2015 +0530, SATISH wrote:
>> [snip]
>>
>>> And am looking for  help for generating unique records (biblio) after
>>> merging both BK and BB. I am trying with following sql, but throwing
>>> errors. Can you please help me over here.
>>> ------------------------------------------------------------
>>> ----------------------------------------------------------------
>>>
>>> SELECT DISTINCT b.biblionumber, b.title, b.author, t.editionstatement,
>>> t.publishercode, t.isbn, i.ccode, count(i.itemnumber) FROM biblio b LEFT
>>> JOIN
>>> biblioitems t USING(biblionumber) LEFT JOIN items i USING(biblionumber)
>>> WHERE items.itype='BB' AND items.itype='BK' GROUP BY b.biblionumber
>>> ---------------------------------------------------------------------
>>>
>>> Error:
>>> The database returned the following error:
>>> Unknown column 'items.itype' in 'where clause'
>>>
>>
>> Two points:  First, after you define "JOIN items i" you refer to 'items'
>> as 'i'.   Second, itype cannot have two values -- it is either BB or BK...
>> Try your last line as:
>>
>> WHERE i.itype='BB' OR i.itype='BK' GROUP BY b.biblionumber;
>>
>> Best -- Paul
>> _______________________________________________
>> Koha mailing list  http://koha-community.org
>> Koha at lists.katipo.co.nz
>> http://lists.katipo.co.nz/mailman/listinfo/koha
>>
>
>
>
> --
>
>
>
>


--