[Koha] List of serials SQL

[Carlos Lopez]

Hi folks

I'm having trouble with an SQL query: I'd like to bring up a list of serial titles on the system their subscription status (open or closed), BUT not all serials have a subscription record (pre-Koha closed subscriptions) AND all serial items have the item type of SERIAL

I thought something like this might work

SELECT DISTINCT b.title, IF(s.closed = 0,"OPEN","CLOSED")
FROM biblio b, items i
LEFT JOIN subscription s ON s.biblionumber = b.biblionumber
WHERE b.biblionumber = i.biblionumber
AND i.itype = "JOURNAL"
ORDER BY b.title

but it keeps failing ("Unknown column 'b.biblionumber' in 'on clause'" - if I take out the "ON s.biblionumber = b.biblionumber" bit I get a list but they're all duplicated, one OPEN the other CLOSED, even if they have no subscription record

Would anyone be able to help us out with this?


With kind regards from the Dalton McCaughey Library Team

Carlos Lopez

Dalton McCaughey Library | 29 College Crescent, Parkville, VICTORIA 3052
Ph: 03 9340 8888 ext.1 | library at dml.vic.edu.au<mailto:library at dml.vic.edu.au> | library.dmlibrary.org.au