[Koha] SQL help please

[Kerrie Stevens]

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 at bywatersolutions.com]
Sent: Wednesday, April 12, 2017 4:59 PM
To: Kerrie Stevens <KStevens at harvest.edu.au>
Cc: koha at 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 at harvest.edu.au<mailto:KStevens at 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 at lists.katipo.co.nz<mailto:Koha at lists.katipo.co.nz>
https://lists.katipo.co.nz/mailman/listinfo/koha