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 < bgkrie...@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 > bgkrie...@gmail.com > > > On Mon, Apr 22, 2013 at 4:18 PM, Nicole Engard <neng...@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 >> > > _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha