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@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@dml.vic.edu.au<mailto:library@dml.vic.edu.au> | library.dmlibrary.org.au
_______________________________________________
Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha