[Koha] Catalogue - SQL Report Help

[Paul A]

At 10:10 AM 2/28/2015 +0530, SATISH wrote:
>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.

You write "separate item types" and "eliminating duplicate records" -- but 
"item type" is a *biblio* attribute (942$c, not repeatable), so if you want 
2 item types, you must have 2 bibliographic records, each with its own 
biblionumber even if they are otherwise identical.

Perhaps what you are looking for is two "branches" -- one "books", one 
"book bank", where you might be able to set different policies...

Best -- Paul

>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 
><<mailto:lis4satish at gmail.com>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 
><<mailto:paul.a at navalmarinearchive.com>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>http://koha-community.org
><mailto:Koha at lists.katipo.co.nz>Koha at lists.katipo.co.nz
><http://lists.katipo.co.nz/mailman/listinfo/koha>http://lists.katipo.co.nz/mailman/listinfo/koha
>
>
>
>
>--
>
>
>
>
>
>
>--
>
>