I'm trying to get a report to show patrons with overdues and if there is a guarantor I want that to show too. But what's happening is the Guarantees are showing as the Guarantors and the Guarantor is showing 2 times cause he has 2 Guarantees - can someone help me with my SQL:
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, FORMAT(SUM(a.amountoutstanding),2) as due FROM borrowers p LEFT JOIN accountlines a USING (borrowernumber) left join borrowers g on (g.guarantorid=p.borrowernumber) WHERE p.dateexpiry < NOW() and p.surname = 'Meave' group by p.borrowernumber, g.borrowernumber ORDER BY p.dateexpiry asc ; +--------------+-------------+------------+---------+---------------+------------+---------------------------------+--------------+--------+ | categorycode | dateofbirth | cardnumber | surname | firstname | dateexpiry | guarantor | relationship | due | +--------------+-------------+------------+---------+---------------+------------+---------------------------------+--------------+--------+ | TRES | 1996-08-24 | P0012129 | Meave | Melina-Maria | 2008-12-04 | | parent | 5.90 | | TRES | 2000-08-30 | P0012128 | Meave | Phoenix-Jonas | 2008-12-04 | | parent | 2.30 | | STRICT | 1956-09-23 | P0012127 | Meave | Jose | 2008-12-04 | Meave, Melina-Maria (P0012129) | | 351.53 | | STRICT | 1956-09-23 | P0012127 | Meave | Jose | 2008-12-04 | Meave, Phoenix-Jonas (P0012128) | | 351.53 | +--------------+-------------+------------+---------+---------------+------------+---------------------------------+--------------+--------+ _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha