[Koha] List of serials SQL

[Jonathan Druart]

Hello Carlos,

You are actually mixing implicit and explicit join.

Try using 2 LEFT JOINs instead:

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

Regards,
Jonathan

Le ven. 21 mai 2021 à 04:24, Carlos Lopez <clopez at dml.vic.edu.au> a écrit :
>
> 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
>
> _______________________________________________
>
> Koha mailing list  http://koha-community.org
> Koha at lists.katipo.co.nz
> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha