[Koha] SQL Query - Items with a Local Cover Image

[Craig Butosi]

Hi Barton,

That did it. Thanks very much! Time to brush up on SQL syntax :S

C

Craig Butosi, MA, MLIS, B Mus (Hons.)
LIBRARY SERVICES MANAGER
the royal conservatory
TELUS Centre for Performance and Learning
273 Bloor Street West Toronto, ON  M5S 1W2
416.408.2824 x338
www.rcmusic.ca<https://webmail.rcmusic.ca/owa/redir.aspx?C=b481823b299d45ee94b0016389bb76e9&URL=http%3a%2f%2fwww.rcmusic.ca%2f>
________________________________
From: Barton Chittenden [barton at bywatersolutions.com]
Sent: Saturday, March 26, 2016 2:49 PM
To: Craig Butosi
Cc: koha
Subject: Re: [Koha] SQL Query - Items with a Local Cover Image


I think this should do what you want.

SELECT
    biblionumber,
    itemcallnumber
FROM
    biblioimages
    LEFT JOIN items using (biblionumber)
WHERE
    imagefile IS NOT NULL

On Mar 26, 2016 1:51 PM, "Craig Butosi" <craig.butosi at rcmusic.ca<mailto:craig.butosi at rcmusic.ca>> wrote:
Sorry, sent prematurely:

The error is :

The following error was encountered:
The database returned the following error:
Column 'biblionumber' in field list is ambiguous
Please check the log for further details.

Any suggestions?

Many thanks,

Craig Butosi, MA, MLIS, B Mus (Hons.)
LIBRARY SERVICES MANAGER
the royal conservatory
TELUS Centre for Performance and Learning
273 Bloor Street West Toronto, ON  M5S 1W2
416.408.2824 x338<tel:416.408.2824%20x338>
www.rcmusic.ca<http://www.rcmusic.ca><https://webmail.rcmusic.ca/owa/redir.aspx?C=b481823b299d45ee94b0016389bb76e9&URL=http%3a%2f%2fwww.rcmusic.ca%2f>
________________________________
From: Craig Butosi
Sent: Saturday, March 26, 2016 1:49 PM
To: Koha
Subject: SQL Query - Items with a Local Cover Image

Hi all,

Koha 3.22 on Ubuntu 14.04 LTS (package install)

I`m trying to write a basic SQL query to pull all items with a local cover image. I`m running Coce now, so I no longer need to upload local cover images to Koha. I need to identify these records so I can remove their images. I have a basic query:

SELECT biblionumber FROM biblioimages
WHERE biblionumber AND imagefile IS NOT NULL

which works, but only returns the biblionumber column from the biblioimages table. I`d like to add a call number column from the items table to this report. I`ve tried this

SELECT biblionumber
FROM biblioimages
LEFT JOIN items
ON biblioimages.biblionumber=items.itemcallnumber
WHERE biblionumber AND imagefile IS NOT NULL

But I get an ambiguity error, and I don`t quite understand how to apply an alias to overcome this. The error is as follows:




Craig Butosi, MA, MLIS, B Mus (Hons.)
LIBRARY SERVICES MANAGER
the royal conservatory
TELUS Centre for Performance and Learning
273 Bloor Street West Toronto, ON  M5S 1W2
416.408.2824 x338
www.rcmusic.ca<http://www.rcmusic.ca><https://webmail.rcmusic.ca/owa/redir.aspx?C=b481823b299d45ee94b0016389bb76e9&URL=http%3a%2f%2fwww.rcmusic.ca%2f>


Note: This email message and any attachments are intended only for the use of the individual to which it is addressed, and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If the recipient of this email is not the intended recipient (or the employee or agent responsible for delivering the email to the intended recipient), you are hereby notified that any review, dissemination, distribution or copying or other use of this message is strictly prohibited. If you have received this communication in error, please notify the sender immediately by return email and delete this message and attachments from your system, thank you.
_______________________________________________
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


Note: This email message and any attachments are intended only for the use of the individual to which it is addressed, and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If the recipient of this email is not the intended recipient (or the employee or agent responsible for delivering the email to the intended recipient), you are hereby notified that any review, dissemination, distribution or copying or other use of this message is strictly prohibited. If you have received this communication in error, please notify the sender immediately by return email and delete this message and attachments from your system, thank you.