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