[Koha-devel] SQL Query : multiple items

[Fridolin SOMERS]

Hie,

It works weel, but it would be more explicite/logic to use itemnumber in 
HAVING :

SELECT
   biblio.biblionumber,
   CONCAT('<a 
href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'\">',biblio.title,'</a>') 
AS title,
   biblio.author,
   GROUP_CONCAT(items.ccode SEPARATOR ', ') AS ccode
FROM biblio LEFT JOIN items USING(biblionumber)
GROUP BY biblio.biblionumber
HAVING COUNT(items.itemnumber)>1;

Regards,

Le 20/01/2014 23:56, Francois Charbonnier a écrit :
> Paul,
>
> By the way, I forgot to mention you could add ccode this way :
>
> SELECT biblio.biblionumber, CONCAT('<a
> href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblionumber,'\">',title,'</a>')
> AS Title, biblio.author, GROUP_CONCAT(items.ccode SEPARATOR ', ') as ccode
> FROM biblio LEFT JOIN items USING(biblionumber)
> GROUP BY biblionumber
> HAVING COUNT(biblionumber)>1;
>
> Hope it helps!
>
> 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/
>
>
>
>
> _______________________________________________
> 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/
>

-- 
Fridolin SOMERS
Biblibre - Pôles support et système
fridolin.somers at biblibre.com