[Koha] SQL help please

[Kerrie Stevens]

Hi Katrin,

Again, this is giving me everything at QLD, not items ONLY held at QLD…

I’m sure there must be a way to do it…

Thanks,

Kerrie Stevens

From: Katrin Fischer [mailto:Katrin.Fischer.83 at web.de]
Sent: Wednesday, April 12, 2017 5:27 PM
To: Kerrie Stevens <KStevens at harvest.edu.au>
Cc: koha at lists.katipo.co.nz
Subject: Aw: [Koha] SQL help please

Hi Kerrie,

I think DISTINCT is not quite what you want here, but this seems to work for one of our libraries with multiple branches:

SELECT
i.itemnumber,
b.title,
b.author,
i.itemcallnumber,
i.barcode,
i.homebranch
FROM
items i
LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber)
WHERE homebranch = "QLD"
AND i.biblionumber not in (SELECT biblionumber FROM items WHERE i.homebranch != "QLD")
ORDER BY i.itemcallnumber

Hope this helps,

Katrin


Gesendet: Mittwoch, 12. April 2017 um 07:20 Uhr
Von: "Kerrie Stevens" <KStevens at harvest.edu.au<mailto:KStevens at harvest.edu.au>>
An: "koha at lists.katipo.co.nz<mailto:koha at lists.katipo.co.nz>" <koha at lists.katipo.co.nz<mailto:koha at lists.katipo.co.nz>>
Betreff: [Koha] SQL help please
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