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

[Barton Chittenden]

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> 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
> 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<
> 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
> https://lists.katipo.co.nz/mailman/listinfo/koha
>