Thank you! That's a step in the right direction :) I have shared it (in your name) on the Koha wiki for others to benefit from. Nicole On Mon, Apr 22, 2013 at 9:19 PM, Bernardo Gonzalez Kriegel < bgkriegel@gmail.com> wrote:

Nicole, you want for each guarantor it's guarantees, Q: it's ok to repeat guarantor information on each guarantee tuple?

you could use something like this (put any data you want in first select, and change order by if needed)

SELECT IFNULL(concat(g.surname, ', ', g.firstname, ' (',g.cardnumber, ')'),'') AS guarantor, IFNULL(concat(p.surname, ', ', p.firstname, ' (',p.cardnumber, ')'),'') AS guarantee FROM ( SELECT * FROM borrowers WHERE guarantorid IS NOT NULL ) AS p LEFT JOIN borrowers AS g ON p.guarantorid = g.borrowernumber ORDER BY g.borrowernumber ;

Bernardo

-- Bernardo Gonzalez Kriegel bgkriegel@gmail.com

On Mon, Apr 22, 2013 at 4:18 PM, Nicole Engard <nengard@gmail.com> wrote:

...

I'm looking to change this report around. It shows patrons with their guarantor information. I want a report that shows me the guarantees for each guarantor instead.

SELECT p.categorycode, p.dateofbirth, p.cardnumber, p.surname, p.firstname, p.dateexpiry, IFNULL(concat(g.surname, ', ', g.firstname, ' (', g.cardnumber, ')'),'') AS guarantor, p.relationship, ifnull(FORMAT(SUM(a.amountoutstanding),2),'0.00') AS due FROM borrowers p LEFT JOIN accountlines a USING (borrowernumber) LEFT JOIN borrowers g ON (p.guarantorid=g.borrowernumber) WHERE p.dateexpiry < NOW() GROUP BY p.borrowernumber ORDER BY p.dateexpiry ASC

Nicole _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha