[Koha-devel] SQL Query : multiple items

[Francois Charbonnier]

Hi Paul,
Try : GROUP BY biblionumber HAVING count(biblionumber)>1
That way, it will work even for biblios without ccode.
François

François Charbonnier,
Chef de produits

Tél.  : (888) 604-2627
francois.charbonnier at inLibro.com <mailto:francois.charbonnier at inLibro.com>

inLibro | pour esprit libre | www.inLibro.com <http://www.inLibro.com>
Le 2014-01-20 16:06, Paul A a écrit :
> I'm going round in circles for what I thought would be easy, and will 
> kick myself later.
>
> Trying to write a "report" that will give me all biblios with more 
> than 1 item:
>
> SELECT
> biblionumber, CONCAT('<a 
> href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblionumber,'\">',title,'</a>') 
> AS Title, author AS Author
> FROM biblio b
> LEFT JOIN items i USING (biblionumber)
> GROUP BY i.ccode
> HAVING COUNT(i.itemnumber)>1;
>
> works syntactically, but certainly does not give me the right answers 
> -- it produces biblios with 0, 1, 2, etc items, and only 83 total 
> lines when I know that there are thousands...
>
> Logic seems to have deserted me. Help please?
>
> As a bonus, I'd appreciate how to add the items.ccode into the report. 
> Adding SELECT ccode FROM items either before or after the JOIN fails.
>
> Thanks and br -- Paul
>
> _______________________________________________
> Koha-devel mailing list
> Koha-devel at lists.koha-community.org
> http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel
> website : http://www.koha-community.org/
> git : http://git.koha-community.org/
> bugs : http://bugs.koha-community.org/

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.koha-community.org/pipermail/koha-devel/attachments/20140120/e47c805c/attachment.html>