klf-signature/25-25.sql

[SQL2XLS] / soc-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 'BD%' group by itemcallnumber ;

-- Duple signature
SELECT count(biblio.biblionumber) as primjeraka,
        callnumbers.count as signatura,
        items.itemcallnumber,
        biblio.author,biblio.title,biblioitems.publicationyear,biblioitems.volume,biblioitems.number,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 and items.itemcallnumber like 'BD%'
GROUP BY biblio.biblionumber
having primjeraka < signatura
order by items.itemcallnumber ;