I have this report that lists all the items in our QLD library but I want to restrict it to items ONLY held in our QLD library and not another branch... I don't know where to put the DISTINCT tag? Please advise: SELECT items.itemnumber, biblio.title, biblio.author, items.itemcallnumber, items.barcode FROM items LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber) WHERE items.homebranch='QLD' ORDER BY items.itemcallnumber Thanks for your help, Kerrie Stevens Harevst Bible College
Kerrie, Just to be sure that I'm understanding you here ... you wan to list the item if it's held at 'QLD', but *not* if it's held at 'QLD' but also held at another branch? I think this will work: SELECT items.itemnumber, biblio.title, biblio.author, items.itemcallnumber, items.barcode FROM items LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber) WHERE NOT CONTAINS ( SELECT * FROM items WHERE items.homebranch != 'QLD' ) ORDER BY items.itemcallnumber On Wed, Apr 12, 2017 at 1:20 AM, Kerrie Stevens <KStevens@harvest.edu.au> wrote:
I have this report that lists all the items in our QLD library but I want to restrict it to items ONLY held in our QLD library and not another branch... I don't know where to put the DISTINCT tag? Please advise:
SELECT items.itemnumber, biblio.title, biblio.author, items.itemcallnumber, items.barcode FROM items LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber) WHERE items.homebranch='QLD' ORDER BY items.itemcallnumber
Thanks for your help,
Kerrie Stevens Harevst Bible College
_______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
That’s right – I want items only held at QLD, bot Qld and another campus. When I run the report you’ve provided, I get the following error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT * FROM items WHERE items.homebranch != 'QLD' ) ORDER BY items.itemcallnu' at line 10 Kerrie Stevens From: Barton Chittenden [mailto:barton@bywatersolutions.com] Sent: Wednesday, April 12, 2017 4:59 PM To: Kerrie Stevens <KStevens@harvest.edu.au> Cc: koha@lists.katipo.co.nz Subject: Re: [Koha] SQL help please Kerrie, Just to be sure that I'm understanding you here ... you wan to list the item if it's held at 'QLD', but *not* if it's held at 'QLD' but also held at another branch? I think this will work: SELECT items.itemnumber, biblio.title, biblio.author, items.itemcallnumber, items.barcode FROM items LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber) WHERE NOT CONTAINS ( SELECT * FROM items WHERE items.homebranch != 'QLD' ) ORDER BY items.itemcallnumber On Wed, Apr 12, 2017 at 1:20 AM, Kerrie Stevens <KStevens@harvest.edu.au<mailto:KStevens@harvest.edu.au>> wrote: I have this report that lists all the items in our QLD library but I want to restrict it to items ONLY held in our QLD library and not another branch... I don't know where to put the DISTINCT tag? Please advise: SELECT items.itemnumber, biblio.title, biblio.author, items.itemcallnumber, items.barcode FROM items LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber) WHERE items.homebranch='QLD' ORDER BY items.itemcallnumber Thanks for your help, Kerrie Stevens Harevst Bible College _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz<mailto:Koha@lists.katipo.co.nz> https://lists.katipo.co.nz/mailman/listinfo/koha
participants (2)
-
Barton Chittenden -
Kerrie Stevens