[Koha] SQL help please

[Bob Birchall]

Hi Kerrie,
You haven't joined the Items table. So:
JOIN items ON (biblio_metadata.biblionumber = items.biblionumber)

HTH,
Bob
Calyx

On 12/06/18 08:43, Kerrie Stevens wrote:
> I have the following report which almost does what I need but I can't quite get it there.
>
> I want to be able get a list of titles and the Dewey from 082$a but I also want to be able to select for which collection I want the results - that is where I'm getting stuck.
> I can select the collection when I run the report, but I get the following error so there is something else missing...
> Error: The following error was encountered: The database returned the following error:  Unknown column 'items.ccode' in 'where clause'
>
> Here is my report so far:
>
> SELECT biblio.biblionumber, biblio.title, biblio.author, concat (ExtractValue( metadata, '//datafield[@tag="082"]/subfield[@code="a"]' ), " ", ExtractValue( metadata, '//datafield[@tag="082"]/subfield[@code="b"]' )) as Dewey
> FROM biblio_metadata
> JOIN biblio ON ( biblio_metadata.biblionumber = biblio.biblionumber )
> WHERE items.ccode=<<Collection|CCODE>>
> ORDER BY biblio.title ASC
>
> It works if I remove the WHERE line, but then I can't select which collection.
>
> Thanks for any help you may provide!