[Koha] Catalogue - SQL Report Help

[Paul A]

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