ang-signature/02-02.sql

[SQL2XLS] / izn-signature-duple / 00-duple-signature.sql

create temporary table callnumbers ( callnumber varchar(255) primary key, count int not null ) ;
insert into callnumbers select itemcallnumber,count(itemcallnumber) from items where itemcallnumber like 'BG%' group by itemcallnumber ;

-- Duple signature
SELECT count(biblio.biblionumber) as primjeraka,
        items.itemcallnumber,
        callnumbers.count as signatura,
        biblio.title,biblio.author,biblioitems.publicationyear,biblio.biblionumber
FROM items
LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber)
LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber)
LEFT JOIN callnumbers on itemcallnumber = callnumbers.callnumber
WHERE items.itemcallnumber IS NOT NULL
GROUP BY biblio.biblionumber
having primjeraka < signatura
order by items.itemcallnumber;